Description:
Two identical queries, one on a view, one on its base table; one uses a filesort, the other doesn't.
root@localhost:(none)> explain select count(pl_from), pl_title from enwiki.pagelinks where pl_namespace = 0 group by pl_namespace, pl_title;
+----+-------------+-----------+------+---------------+--------------+---------+-------+-----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------+-----------+--------------------------+
| 1 | SIMPLE | pagelinks | ref | pl_namespace | pl_namespace | 4 | const | 116395126 | Using where; Using index |
+----+-------------+-----------+------+---------------+--------------+---------+-------+-----------+--------------------------+
1 row in set (0.00 sec)
root@localhost:(none)> explain select count(pl_from), pl_title from enwiki_p.pagelinks where pl_namespace = 0 group by pl_namespace, pl_title;
+----+-------------+-----------+------+---------------+--------------+---------+-------+-----------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+--------------+---------+-------+-----------+-----------------------------------------------------------+
| 1 | SIMPLE | pagelinks | ref | pl_namespace | pl_namespace | 4 | const | 116395153 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-----------+------+---------------+--------------+---------+-------+-----------+-----------------------------------------------------------+
1 row in set (0.01 sec)
root@localhost:(none)> show create table enwiki.pagelinks;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pagelinks | CREATE TABLE `pagelinks` (
`pl_from` int(8) unsigned NOT NULL default '0',
`pl_namespace` int(11) NOT NULL default '0',
`pl_title` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost:(none)> show create view enwiki_p.pagelinks;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View | Create View |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pagelinks | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `enwiki_p`.`pagelinks` AS select `enwiki`.`pagelinks`.`pl_from` AS `pl_from`,`enwiki`.`pagelinks`.`pl_namespace` AS `pl_namespace`,`enwiki`.`pagelinks`.`pl_title` AS `pl_title` from `enwiki`.`pagelinks` |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
How to repeat:
See description.
Suggested fix:
Unknown.
