MySQL join optimization - bypassing filesort

Question

still having problems optimizing a MySQL LEFT JOIN. That query takes 0.13 seconds to complete instead of 0.00 for the next (simplified one).

I'd like to achieve 0.00 or so for that query.

I've tried creating indexes and combo indexes all over. Doesn't change much. Basically as long as there's FILESORT in EXPLAIN, it's slow. I'm not sure what to do... create an index across tables? Does it even exist?

Thank you.

The culprit:

SELECT 
  SQL_NO_CACHE p.id 
FROM 1_posts p 
  INNER JOIN 1_topics t 
    ON (p.cid = t.cid && p.container = t.id) 
WHERE 
  t.cid = 1010699 
ORDER BY 
  p.id DESC 
LIMIT 1;

The EXPLAIN output:

+----+-------------+-------+------+-------------------+-------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys     | key   | key_len | ref                 | rows | Extra                                        |
+----+-------------+-------+------+-------------------+-------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | t     | ref  | PRIMARY,cid,cid_2 | cid   | 4       | const               |  216 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref  | PRIMARY,cid,cid_2 | cid_2 | 8       | const,forumdb.t.id |   12 |                                              |
+----+-------------+-------+------+-------------------+-------+---------+---------------------+------+----------------------------------------------+

Now, the same simplified query that works fine (uses indexes etc. The only difference is between the brackets):

SELECT 
  SQL_NO_CACHE p.id 
FROM 
  1_posts p 
  INNER JOIN 1_topics t 
    ON (p.cid = t.cid) 
WHERE 
  t.cid = 1010699 
ORDER BY 
  p.id DESC 
LIMIT 1;

EXPLAINed:

+----+-------------+-------+-------+-------------------+---------+---------+-------+-------+--------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows  | Extra                    |
+----+-------------+-------+-------+-------------------+---------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | p     | range | PRIMARY,cid,cid_2 | PRIMARY | 4       | NULL  | 31720 | Using where; Using index |
|  1 | SIMPLE      | t     | ref   | PRIMARY,cid,cid_2 | cid_2   | 4       | const |   194 | Using index              |
+----+-------------+-------+-------+-------------------+---------+---------+-------+-------+--------------------------+

Tables:

CREATE TABLE `1_posts` (
  `cid` int(20) unsigned NOT NULL DEFAULT '0',
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `container` int(20) unsigned NOT NULL DEFAULT '0',
  `creator` int(20) unsigned NOT NULL DEFAULT '0',
  `ref` int(20) unsigned DEFAULT NULL,
  `timestamp` int(20) unsigned NOT NULL DEFAULT '0',
  `posticon` tinyint(11) DEFAULT NULL,
  `last_edited_ts` int(10) unsigned DEFAULT NULL,
  `last_edited_by` int(20) unsigned DEFAULT NULL,
  `signature` varchar(250) DEFAULT NULL,
  `client_ip` int(10) unsigned NOT NULL DEFAULT '0',
  `data_format` tinyint(20) unsigned DEFAULT NULL,
  `use_bbcode` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `use_smileys` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `topic_hash` int(10) unsigned NOT NULL DEFAULT '0',
  `del_ts` int(10) unsigned NOT NULL DEFAULT '0',
  `del_reason` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`cid`,`id`),
  UNIQUE KEY `cid` (`cid`,`topic_hash`,`container`,`id`,`del_ts`),
  KEY `cid_2` (`cid`,`container`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


CREATE TABLE `1_topics` (
  `cid` int(10) unsigned NOT NULL DEFAULT '0',
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `container` int(20) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  `creator` int(20) unsigned NOT NULL DEFAULT '0',
  `last_modified` int(20) unsigned NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '0',
  `closed` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `sticky` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `last_post_id` int(20) unsigned DEFAULT NULL,
  `num_posts` int(10) unsigned DEFAULT NULL,
  `lp_ts` int(20) unsigned NOT NULL DEFAULT '0',
  `posticon` smallint(5) unsigned DEFAULT NULL,
  `hidden` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `topic_change_ts` int(10) unsigned NOT NULL DEFAULT '0',
  `topic_hash` int(10) unsigned NOT NULL DEFAULT '0',
  `forum_hash` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`cid`,`id`),
  KEY `container` (`container`),
  KEY `last_modified` (`last_modified`),
  KEY `sticky` (`sticky`),
  KEY `topic_hash` (`topic_hash`),
  KEY `forum_hash` (`forum_hash`),
  KEY `cid` (`cid`,`id`),
  KEY `cid_2` (`cid`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=211963 DEFAULT CHARSET=latin1

This is the EXPLAIN output after Gordon's index added:

+----+-------------+-------+------+-------------------------------+-------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys                 | key   | key_len | ref                 | rows | Extra                                        |
+----+-------------+-------+------+-------------------------------+-------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | t     | ref  | PRIMARY,cid,cid_2             | cid   | 4       | const               |  212 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref  | PRIMARY,cid,cid_2,cid_3,cid_4 | cid_3 | 8       | const,forumdb.t.id |   11 | Using index                                  |
+----+-------------+-------+------+-------------------------------+-------+---------+---------------------+------+----------------------------------------------+

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-08-13T10:47:55

This version uses the right indexes:

SELECT SQL_NO_CACHE p.id
FROM 1_posts p INNER JOIN
     1_topics t
     ON (p.cid = t.cid)
WHERE t.cid = 1010699
ORDER BY p.id DESC LIMIT 1;

This version does not:

SELECT SQL_NO_CACHE p.id
FROM 1_posts p INNER JOIN
     1_topics t
     ON (p.cid = t.cid && p.container = t.id);
WHERE t.cid = 1010699
ORDER BY p.id DESC
LIMIT 1;

In the first, MySQL can use the index on l_posts(cid, id) first for the where clause (cid column is first in the index) and then for the join (same column). Then it can use the same index for the sort -- id is the next column in the index. (By the way, this is using a feature of the MySQL optimizer that propagates the = condition in the where clause from t to p.)

For the second, MySQL can use the l_posts(cid, container) index for the where and join. But then the same index cannot be used for the sort. The engine decides that a filesort is better than trying to merge two different indexes.

To get the second version to use an index, define one on l_posts(cid, container, id).

MySQL join optimization - bypassing filesort

2 Answers