CODEWITHSUNDEEP
mysqlsql
Bintz
Bintz

Reputation: 801

MySQL query not using primary key, I don't understand why

I have a table called product categories. Structure is as follows:

+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| sku           | char(50)   | NO   | PRI |         |       |
| sorting_field | char(20)   | NO   | PRI |         |       |
| category      | char(255)  | NO   | PRI |         |       |
| hide          | tinyint(1) | NO   | PRI | 0       |       |
+---------------+------------+------+-----+---------+-------+

The following query runs an excessive amount of time on this table:

SELECT category FROM product_categories WHERE hide!=1 AND sorting_field="item_type"  GROUP BY category;

When I run EXPLAIN, I get the following result:

+----+-------------+-----------------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
| id | select_type | table              | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                                     |
+----+-------------+-----------------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | product_categories | index | NULL          | PRIMARY | 976     | NULL | 43568 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-----------------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+

I don't understand why this is happening at all! Why is the primary key not considered a possible key? This just doesn't make sense to me. By the way, here is the result of SHOW KEYS on this table:

+-----------------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| product_categories |          0 | PRIMARY  |            1 | sku           | A         |        8713 |     NULL | NULL   |      | BTREE      |         |
| product_categories |          0 | PRIMARY  |            2 | sorting_field | A         |       43568 |     NULL | NULL   |      | BTREE      |         |
| product_categories |          0 | PRIMARY  |            3 | category      | A         |       43568 |     NULL | NULL   |      | BTREE      |         |
| product_categories |          0 | PRIMARY  |            4 | hide          | A         |       43568 |     NULL | NULL   |      | BTREE      |         |
+-----------------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

Can anybody shed light on this dilemma for me?

Upvotes: 0

Views: 2463

Answers (2)

Milimetric
Milimetric

Reputation: 13549

The possible_keys column of EXPLAIN is just a list of possibilities. The key column is what's actually going to be used in the query, and in your case, it's showing that it will use the PRIMARY.

Upvotes: 1

gieffe
gieffe

Reputation: 336

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. Your index is (sku, sorting_field, category, hide), so you have index search capabilities on (sku), (sku, sorting_field), (sku, sorting_field, category) and (sku, sorting_field, category, hide).

In other words if you change your primary key to (sorting_field, hide, sku, category), primary key became a possible key of your query.

However i think you could find better solutions. For example:

create table product_categories (
sku char(50) not null,
sorting_field char(20) not null,
category char(255) not null,
hide tinyint(1) not null,
Primary key (sku, category),
key sorting_field (sorting_field, hide))

Upvotes: 2

Related Questions