Description:
Reproduced this issue on two servers, both running 64-bit 5.1.46 with standard InnoDB on Solaris 10 Update 8 x86.
river@sql-s2-fast:dewiki_p> SELECT dl.*, page_title, COUNT(NULLIF(p.page_namespace=0,FALSE)) as c from u_river.dewiki_dab_links dl INNER JOIN dewiki_p.page p ON dl.page_id = p.page_id WHERE dl.date='2010-05-17' AND p.page_namespace IN (0,10,14) GROUP BY dl.dab_main_title order by c LIMIT 20;
+------------+---------+--------+----------------+-----------------------+--------+
| date | page_id | dab_id | dab_main_title | page_title | c |
+------------+---------+--------+----------------+-----------------------+--------+
| 2010-05-17 | 5850 | 128283 | NULL | 1920er | 24202 |
| 2010-05-17 | 4258476 | 232415 | | Badanhausen_(Kinding) | 169987 |
+------------+---------+--------+----------------+-----------------------+--------+
2 rows in set (8.30 sec)
river@sql-s2-fast:dewiki_p> SELECT dl.*, page_title, COUNT(NULLIF(p.page_namespace=0,FALSE)) as c from u_river.dewiki_dab_links dl INNER JOIN dewiki_p.page p ON dl.page_id = p.page_id WHERE dl.date='2010-05-17' AND p.page_namespace IN (0,10,14) GROUP BY dl.dab_main_title LIMIT 5;
+------------+---------+---------+----------------+----------------------+---+
| date | page_id | dab_id | dab_main_title | page_title | c |
+------------+---------+---------+----------------+----------------------+---+
| 2010-05-17 | 5015402 | 5014872 | !! | ‼ | 2 |
| 2010-05-17 | 14692 | 22811 | 0 | Null | 4 |
| 2010-05-17 | 27611 | 27608 | 08/15 | Null-acht-fünf-zehn | 6 |
| 2010-05-17 | 246114 | 5202696 | 1-Cent-Münze | Cent_(Währung) | 1 |
| 2010-05-17 | 1235090 | 1915462 | 1._Armee | 1._Deutsche_Armee | 1 |
+------------+---------+---------+----------------+----------------------+---+
5 rows in set (0.00 sec)
river@sql-s2-fast:dewiki_p> explain SELECT dl.*, page_title, COUNT(NULLIF(p.page_namespace=0,FALSE)) as c from u_river.dewiki_dab_links dl INNER JOIN dewiki_p.page p ON dl.page_id = p.page_id WHERE dl.date='2010-05-17' AND p.page_namespace IN (0,10,14) GROUP BY dl.dab_main_title order by c LIMIT 5;
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | dl | index | PRIMARY,date,page_id | dab_main_title | 258 | NULL | 80 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | page | eq_ref | PRIMARY,name_title | PRIMARY | 4 | u_river.dl.page_id | 1 | Using where |
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+-----------------------------------------------------------+
2 rows in set (0.01 sec)
river@sql-s2-fast:dewiki_p> explain SELECT dl.*, page_title, COUNT(NULLIF(p.page_namespace=0,FALSE)) as c from u_river.dewiki_dab_links dl INNER JOIN dewiki_p.page p ON dl.page_id = p.page_id WHERE dl.date='2010-05-17' AND p.page_namespace IN (0,10,14) GROUP BY dl.dab_main_title LIMIT 20;
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+--------------------------+
| 1 | SIMPLE | dl | index | PRIMARY,date,page_id | dab_main_title | 258 | NULL | 80 | Using where; Using index |
| 1 | SIMPLE | page | eq_ref | PRIMARY,name_title | PRIMARY | 4 | u_river.dl.page_id | 1 | Using where |
+----+-------------+-------+--------+----------------------+----------------+---------+--------------------+------+--------------------------+
2 rows in set (0.00 sec)
The only difference in the query is the ORDER BY, but the result is significantly different.
root@sql-s2-fast:(none)> show create table dewiki.page\G
*************************** 1. row ***************************
Table: page
Create Table: CREATE TABLE `page` (
`page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`page_namespace` int(11) NOT NULL DEFAULT '0',
`page_title` varbinary(255) NOT NULL DEFAULT '',
`page_restrictions` tinyblob NOT NULL,
`page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
`page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT '0',
`page_is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
`page_random` double unsigned NOT NULL DEFAULT '0',
`page_touched` varbinary(14) NOT NULL DEFAULT '',
`page_latest` int(8) unsigned NOT NULL DEFAULT '0',
`page_len` int(8) unsigned NOT NULL DEFAULT '0',
`page_no_title_convert` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`page_id`),
UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
KEY `page_random` (`page_random`),
KEY `page_len` (`page_len`)
) ENGINE=InnoDB AUTO_INCREMENT=5392991 DEFAULT CHARSET=binary
1 row in set (0.00 sec)
root@sql-s2-fast:(none)> show create table u_river.dewiki_dab_links\G
*************************** 1. row ***************************
Table: dewiki_dab_links
Create Table: CREATE TABLE `dewiki_dab_links` (
`date` date NOT NULL DEFAULT '0000-00-00',
`page_id` int(11) NOT NULL DEFAULT '0',
`dab_id` int(11) NOT NULL DEFAULT '0',
`dab_main_title` varbinary(255) DEFAULT NULL,
PRIMARY KEY (`date`,`page_id`,`dab_id`),
KEY `date` (`date`),
KEY `page_id` (`page_id`),
KEY `dab_id` (`dab_id`),
KEY `dab_main_title` (`dab_main_title`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
I have dumped both tables (102MB total) and can upload the dump to ftp.mysql.com if necessary.
How to repeat:
Load dump, run both queries, observe output.