CODEWITHSUNDEEP
mysql
Ralfeus
Ralfeus

Reputation: 815

MySQL index isn't used in simple select query

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.

Upvotes: 4

Views: 73

Answers (1)

zerkms
zerkms

Reputation: 255155

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.

Upvotes: 1

Related Questions