MySQL index isn't used in simple select query

Question

I have a table

CREATE TABLE `pd` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `meta_description` varchar(255) NOT NULL,
  `meta_keyword` varchar(255) NOT NULL,
  `seo_title` varchar(255) NOT NULL,
  `seo_h1` varchar(255) NOT NULL,
  PRIMARY KEY (`product_id`,`language_id`),
  KEY `language_id` (`description`(128),`language_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=47019 DEFAULT CHARSET=utf8;

When I run this query

EXPLAIN SELECT * FROM `pd` ORDER BY product_id;

I get this result:

+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+
|  1 | SIMPLE      | pd    | index | NULL          | PRIMARY | 8       | NULL | 139551 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------+

When I run this query

EXPLAIN SELECT * FROM `pd` ORDER BY name;

I get this result:

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
|  1 | SIMPLE      | pd    | ALL  | NULL          | NULL | NULL    | NULL | 137762 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+

Why in second case index isn't used? Only difference I see is product_id is part of primary key and name is non-unique index.

The fact that product_id is an int (fixed width type) and name is a varchar (variable width type) is a pretty big difference. — vangelion
– vangelion, Commented Dec 1, 2013 at 9:52
@vangelion: and what is that difference? In the index the prefix for varchar is stored anyway. M? — zerkms
– zerkms, Commented Dec 1, 2013 at 9:52

zerkms · Accepted Answer · 2013-12-01 09:52:11Z

1

As soon as the name isn't a clustered key - it will be much more expensive to read the index first and seek for every row one by one in data.

So mysql (and I think that any other would DBMS) decdies to read the data and sort it in-memory.

answered Dec 1, 2013 at 9:52

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL index isn't used in simple select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related