Description:
While optimising a query using "explain select " we found the following misleading outputs:
mysql> explain select f.idFile,f.idClass,f.idStorageContainer,f.SharingContainer,n.RelativeName,d.AbsolutePath,n.mtime from NameSpaceEntry n use index (index2),DirEntry d use index (PRIMARY), FileEntry f use index (index1) where (n.idNameSpaceEntry = f.idFile) AND (n.ParentEntry=d.idDirEntry) AND f.idFile is not NULL AND f.Hash is NULL AND f.WriteLock = 0 AND n.mtime > '-1' AND f.idStorageContainer <> 0 AND f.idFile > 0 AND f.ReferenceCount = 0 AND n.WORMActive = 1 AND f.Valid = 'V
ALID' ORDER BY n.mtime;
+-------+--------+---------------+---------+---------+---------------+----------+----------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+---------+---------+---------------+----------+----------------------------------------------+
| f | range | index1 | index1 | 8 | NULL | 3537335 | Using where; Using temporary; Using filesort |
| n | ALL | index2 | NULL | NULL | NULL | 30014912 | Using where |
| d | eq_ref | PRIMARY | PRIMARY | 8 | n.ParentEntry | 1 | |
+-------+--------+---------------+---------+---------+---------------+----------+----------------------------------------------+
3 rows in set (0.00 sec)
mysql> explain select f.idFile,f.idClass,f.idStorageContainer,f.SharingContainer,n.RelativeName,d.AbsolutePath,n.mtime from NameSpaceEntry n use index (index2),DirEntry d use index (PRIMARY), FileEntry f use index (index1) where (n.idNameSpaceEntry = f.idFile) AND (n.ParentEntry=d.idDirEntry) AND f.idFile is not NULL AND f.Hash is NULL AND f.WriteLock = 0 AND n.mtime > '-1' AND f.idStorageContainer <> 0 AND f.idFile > 0 AND f.ReferenceCount = 0 AND n.WORMActive = 1 AND f.Valid = 'V
ALID' ORDER BY n.mtime;
+-------+--------+---------------+---------+---------+--------------------------------------+----------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+---------+---------+--------------------------------------+----------+-------------+
| n | index | index2 | index2 | 43 | NULL | 30014937 | Using where |
| d | eq_ref | PRIMARY | PRIMARY | 8 | n.ParentEntry | 1 | |
| f | ref | index1 | index1 | 82 | n.idNameSpaceEntry,const,const,const | 5081496 | Using where |
+-------+--------+---------------+---------+---------+--------------------------------------+----------+-------------+
3 rows in set (0.00 sec)
Also in the API the select statement executes creating temporary files, which increases the response time.
How to repeat:
Run the "explain " on any sql syntax consequtively.
