Bug #15953 | why did have different result after using partition? | ||
---|---|---|---|
Submitted: | 23 Dec 2005 6:13 | Modified: | 26 Jan 2006 14:48 |
Reporter: | zhenxing zhai | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.1.3 | OS: | Linux (linux) |
Assigned to: | CPU Architecture: | Any |
[23 Dec 2005 6:13]
zhenxing zhai
[23 Dec 2005 9:33]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW CREATE TABLE for your weblogentry and weblogentry_new tables. Send also the EXPLAIN results for the (same) queries to the tables giving you different row counts.
[26 Dec 2005 2:48]
zhenxing zhai
mysql> show create table weblogentry; | weblogentry |CREATE TABLE `weblogentry` ( `ANCHOR` varchar(255) NOT NULL default '', `TITLE` varchar(255) NOT NULL default '', `pubtime` timestamp NOT NULL default CURRENT_TIMESTAMP, `updatetime` timestamp NOT NULL default '0000-00-00 00:00:00', `PUBLISHENTRY` tinyint(4) NOT NULL default '0', `ALLOWCOMMENTS` tinyint(4) NOT NULL default '0', `EXTRATEXT2` varchar(255) default NULL, `KEYWORDS` varchar(255) default NULL, `ISPASS` tinyint(4) default NULL, `id` int(11) NOT NULL auto_increment, `WEBSITEID` int(11) default NULL, `CATEGORYID` int(11) default NULL, `TEXT` text, `EXTRATEXT` text, `userid` int(11) default '0', PRIMARY KEY (`id`), KEY `idx_weblogentry_title` (`TITLE`), KEY `idx_weblogentry_publishentry` (`PUBLISHENTRY`), KEY `idx_weblogentry_allowcomments` (`ALLOWCOMMENTS`), KEY `idx_weblogentry_ispass` (`ISPASS`), KEY `idx_weblogentry_websiteid` (`WEBSITEID`), KEY `idx_weblogentry_categoryid` (`CATEGORYID`), KEY `idx_weblogentry_userid` (`userid`), KEY `idx_weblogentry_pub_ispass_id` (`PUBLISHENTRY`,`ISPASS`,`id`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk | 1 row in set (0.00 sec) mysql> show create table weblogentry_new; | Table | Create Table | 1 row in set (0.00 sec) explain plans as follows : mysql> explain 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; +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+ | 1 | PRIMARY | e | range | PRIMARY,idx_weblogentry_publishentry,idx_weblogentry_ispass,idx_weblogentry_userid,idx_weblogentry_pub_ispass_id | idx_weblogentry_pub_ispass_id | 7 | NULL | 159 | Using where | | 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | riji.e.userid | 1 | | | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+ 3 rows in set (0.01 sec) mysql> explain 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; +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+ | 1 | PRIMARY | e | range | PRIMARY,idx_weblogentry_publishentry,idx_weblogentry_ispass,idx_weblogentry_userid,idx_weblogentry_pub_ispass_id | idx_weblogentry_pub_ispass_id | 7 | NULL | 81 | Using where | | 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | riji.e.userid | 1 | | | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+---------------+------+------------------------------+ 3 rows in set (0.42 sec) mysql> select count(1) from weblogentry; +----------+ | count(1) | +----------+ | 1295377 | +----------+ 1 row in set (0.00 sec) mysql> select count(1) from weblogentry_new; +----------+ | count(1) | +----------+ | 1295377 | +----------+ 1 row in set (0.00 sec)
[26 Dec 2005 14:48]
Valeriy Kravchuk
Sorry, but there is yet another table involved in you query, rolleruser. So, I need CREATE TABLE statement for it, as well as for weblogentry_new (because your SHOW CREATE TABLE result for it is empty in previous comment). How exactly is it partitioned? To summartize: I need all the SQL statements (and data) to repeat the problem. The smallest possible set of data to reproduce it is desirable.
[27 Jan 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".