MySQL Forums
Forum List  »  Partitioning

why did have different result after using partition?
Posted by: zhenxing zhai
Date: December 21, 2005 02:36AM

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
why did have different result after using partition?
3676
December 21, 2005 02:36AM


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.