Bug #32527 when selecting from a view, filesort is needlessly used

Submitted: 20 Nov 2007 14:40 Modified: 8 Jan 2008 19:05
Reporter: River Tarnell Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.0.42 OS:Solaris (Solaris 10 U4 patch 120012-14)
Assigned to: Assigned Account CPU Architecture:Any

[20 Nov 2007 14:40] River Tarnell
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.
[8 Dec 2007 19:05] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51 or 5.0.52, and inform about the results.
[9 Jan 2008 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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
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.