MySQL index slowing down query

Question

MySQL Server version: 5.0.95
Tables All: InnoDB

I am having an issue with a MySQL db query. Basically I am finding that if I index a particular varchar(50) field tag.name, my queries take longer (x10) than not indexing the field. I am trying to speed this query up, however my efforts seem to be counter productive.

The culprit line and field seems to be:

WHERE `t`.`name` IN ('news','home')

I have noticed that if i query the tag table directly without a join using the same criteria and with the name field indexed, i do not have the issue.. It actually works faster as expected.

EXAMPLE Query **

      SELECT `a`.*, `u`.`pen_name`
        FROM `tag_link` `tl`
  INNER JOIN `tag` `t`
          ON `t`.`tag_id` = `tl`.`tag_id`
  INNER JOIN `article` `a` 
          ON `a`.`article_id` = `tl`.`link_id`
  INNER JOIN `user` `u`
          ON `a`.`user_id` = `u`.`user_id`   
       WHERE `t`.`name` IN ('news','home')
         AND `tl`.`type` = 'article'
         AND `a`.`featured` = 'featured'
    GROUP BY `article_id`
       LIMIT 0 , 5

EXPLAIN with index **

| id | select_type | table | type   | possible_keys            | key     | key_len | ref               | rows | Extra                                                     |
+----+-------------+-------+--------+--------------------------+---------+---------+-------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | t     | range  | PRIMARY,name             | name    | 152     | NULL              |    4 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | tl    | ref    | tag_id,link_id,link_id_2 | tag_id  | 4       | portal.t.tag_id   |   10 | Using where                                               | 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,fk_article_user1 | PRIMARY | 4       | portal.tl.link_id |    1 | Using where                                               | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                  | PRIMARY | 4       | portal.a.user_id  |    1 |                                                           | 
+----+-------------+-------+--------+--------------------------+---------+---------+-------------------+------+-----------------------------------------------------------+

EXPLAIN without index **

+----+-------------+-------+--------+--------------------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys            | key     | key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+--------------------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | a     | index  | PRIMARY,fk_article_user1 | PRIMARY | 4       | NULL                | 8742 | Using where | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                  | PRIMARY | 4       | portal.a.user_id    |    1 |             | 
|  1 | SIMPLE      | tl    | ref    | tag_id,link_id,link_id_2 | link_id | 4       | portal.a.article_id |    3 | Using where | 
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                  | PRIMARY | 4       | portal.tl.tag_id    |    1 | Using where | 
+----+-------------+-------+--------+--------------------------+---------+---------+---------------------+------+-------------+

TABLE CREATE

CREATE TABLE `tag` (
  `tag_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `type` enum('layout','image') NOT NULL,
  `create_dttm` datetime default NULL,
  PRIMARY KEY  (`tag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43077 DEFAULT CHARSET=utf8 

INDEXS

SHOW INDEX FROM tag_link;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tag_link |          0 | PRIMARY  |            1 | tag_link_id | A         |       42023 |     NULL | NULL   |      | BTREE      |         |
| tag_link |          1 | tag_id   |            1 | tag_id      | A         |       10505 |     NULL | NULL   |      | BTREE      |         |
| tag_link |          1 | link_id  |            1 | link_id     | A         |       14007 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

SHOW INDEX FROM article;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| article |          0 | PRIMARY          |            1 | article_id  | A         |        5723 |     NULL | NULL   |      | BTREE      |         |
| article |          1 | fk_article_user1 |            1 | user_id     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| article |          1 | create_dttm      |            1 | create_dttm | A         |        5723 |     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Final Solution It seems that MySQL is just sorted the data incorrectly. In the end it turned out faster to look at the tag table as a sub query returning the ids.

Answer 1

It seems that article_id is the primary key for the article table.

Since you're grouping by article_id, MySQL needs to return the records in order by that column, in order to perform the GROUP BY.

You can see that without the index, it scans all records in the article table, but they're at least in order by article_id, so no later sort is required. The LIMIT optimization can be applied here, since it's already in order, it can just stop after it gets five rows.

In the query with the index on tag.name, instead of scanning the entire articles table, it utilizes the index, but against the tag table, and starts there. Unfortunately, when doing this, the records must later be sorted by article.article_id in order to complete the GROUP BY clause. The LIMIT optimization can't be applied since it must return the entire result set, then order it, in order to get the first 5 rows.

In this case, MySQL just guesses wrongly.

Without the LIMIT clause, I'm guessing that using the index is faster, which is maybe what MySQL was guessing.

Answer 2

How big are your tables? I noticed in the first explain you have a "Using temporary; Using filesort" which is bad. Your query is likely being dumped to disc which makes it way slower than in memory queries. Also try to avoid using "select *" and instead query the minimum fields needed.