MySQL Forums
Forum List  »  Partitioning

Re: why did have different result after using partition?
Posted by: Mikael Ronström
Date: December 22, 2005 01:35PM

Hi,
It seems likely that you have hit a bug so please file a bug report.
Try and see if the select max-part of the query delivers the same
result. This is an area that could be affected by partitioning.

Rgrds Mikael

zhenxing zhai wrote:
> I hava two tables,record number as follows:
>
> mysql> select count(1) from weblogentry;
> +----------+
> | count(1) |
> +----------+
> | 1295377 |
> +----------+
> 1 row in set (0.00 sec)
>
>
> mysql> select count(1) from rolleruser;
> +----------+
> | count(1) |
> +----------+
> | 626007 |
> +----------+
> 1 row in set (0.00 sec)
>
> I make hash partitions to table weblogentry ,new
> table named weblogentry_new,record number as
> follow:
>
> mysql> select count(1) from weblogentry_new;
> +----------+
> | count(1) |
> +----------+
> | 1295377 |
> +----------+
> 1 row in set (0.00 sec)
>
> now,I have a join query to weblogentry and
> rolleruser,statment as follows:
>
> select count(1) from weblogentry e,rolleruser u
> where e.id>((select max(id) from
> weblogentry)-100000) and e.publishentry='2' and
> e.ispass=1 and e.userid=u.id order by e.id desc
> limit 480;
>
> the result is:
> mysql> select count(1) from weblogentry
> e,rolleruser u where e.id>((select max(id) from
> weblogentry)-100000) and e.publishentry='2' and
> e.ispass=1 and e.userid=u.id order by e.id desc
> limit 480;
> +----------+
> | count(1) |
> +----------+
> | 269 |
> +----------+
> 1 row in set (0.00 sec)
>
> replace table weblogentry_new ,the result is:
> mysql> select count(1) from weblogentry_new
> e,rolleruser u where e.id>((select max(id) from
> weblogentry_new)-100000) and e.publishentry='2'
> and e.ispass=1 and e.userid=u.id order by e.id
> desc limit 480;
> +----------+
> | count(1) |
> +----------+
> | 269 |
> +----------+
> 1 row in set (0.00 sec)
>
> the result is equal,but when I replace count(1) to
> e.id,e.title,e.publishentry,u.username,u.fullname
>
> the statment become :
>
> select
> e.id,e.title,e.publishentry,u.username,u.fullname
> from weblogentry e,rolleruser u where
> e.id>((select max(id) from weblogentry)-100000)
> and e.publishentry='2' and e.ispass=1 and
> e.userid=u.id order by e.id desc limit 480;
>
> mysql> select
> e.id,e.title,e.publishentry,u.username,u.fullname
> from weblogentry e,rolleruser u where
> e.id>((select max(id) from weblogentry)-100000)
> and e.publishentry='2' and e.ispass=1 and
> e.userid=u.id order by e.id desc limit 480;
> ....
> 269 rows in set (0.01 sec)
>
> the result num is stilll 269,but replace
> weblogentry to weblogentry_new:
>
> mysql> select
> e.id,e.title,e.publishentry,u.username,u.fullname
> from weblogentry_new e,rolleruser u where
> e.id>((select max(id) from
> weblogentry_new)-100000) and e.publishentry='2'
> and e.ispass=1 and e.userid=u.id order by e.id
> desc limit 480;
> .....
> 66 rows in set (0.00 sec)
>
> the result num becomes 66,why????

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: why did have different result after using partition?
2687
December 22, 2005 01:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.