CODEWITHSUNDEEP
mysqloptimizationindexingquery-optimizationdatabase-schema
jrichardlai
jrichardlai

Reputation: 3357

Why is MySQL not using my index ? Optimizing mysql select query

I am trying to optimize a MySQL select request:

SELECT * FROM `sales` 
WHERE ((sales.private = false AND (sales.buyer_id IS NULL OR NOT sales.buyer_id=142)
  AND (sales.merchand_id IS NULL OR NOT sales.merchand_id=142)
  AND (sales.private_item = false) )
  AND ((sales.buyer_id=32 OR sales.merchand_id=32)
  AND (sales.admin=0 AND NOT sales.type IN ('book'))))
ORDER BY sales.created_at DESC, sales.id DESC LIMIT 0, 10;

The schema of the table is 

mysql> SHOW columns from sales;
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | int(11)      | NO   | PRI | NULL    | auto_increment |
| type                   | varchar(255) | YES  | MUL | NULL    |                |
| buyer_id               | int(11)      | YES  | MUL | NULL    |                |
| merchand_id            | int(11)      | YES  | MUL | NULL    |                |
| private                | tinyint(1)   | YES  |     | 0       |                |
| admin                  | tinyint(1)   | YES  |     | 0       |                |
| created_at             | datetime     | YES  |     | NULL    |                |
| updated_at             | datetime     | YES  |     | NULL    |                |
| country_id             | int(11)      | YES  | MUL | 0       |                |
| private_item           | tinyint(1)   | YES  |     | 0       |                |
+------------------------+--------------+------+-----+---------+----------------+

The indexes are:

    mysql> show indexes from sales;
+-----------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name                           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sales |          0 | PRIMARY                            |            1 | id          | A         |      286509 |     NULL | NULL   |      | BTREE      |         |
| sales |          1 | index_sales_on_type                |            1 | type        | A         |         123 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_on_buyer_id            |            1 | buyer_id    | A         |       40929 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_on_merchand_id         |            1 | merchand_id | A         |       40929 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_on_country_id          |            1 | country_id  | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_on_type_and_country_id |            1 | type        | A         |         151 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_on_type_and_country_id |            2 | country_id  | A         |         428 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_viewed                 |            1 | buyer_id    | A         |       35813 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_viewed                 |            2 | merchand_id | A         |      286509 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_viewed                 |            3 | private_item| A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_viewed                 |            4 | admin       | A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_viewed                 |            5 | type        | A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_viewed                 |            6 | private     | A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
| sales |          1 | index_sales_viewed                 |            7 | created_at  | A         |      285009 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

When doing the query it uses the index_sales_on_type_and_country_id even if there is no country_id in the query...

The query takes 2.5 seconds with this index.

But when I use USE INDEX(index_sales_viewed) it goes down to 0.2 seconds.

Here is the EXPLAIN of the query:

+----+-------------+-----------------+------+----------------------------------------------+------+---------+------+--------+---------------------
| id | select_type | table | type  | possible_keys  | key                                | key_len | ref  | rows   | Extra                       | 
+----+-------------+-----------------+------+----------------------------------------------+------+---------+------+--------+---------------------
|  1 | SIMPLE      | sales | range |  see bellow    | index_sales_on_type_and_country_id | 258     | NULL | 208725 | Using where; Using filesort | 
+----+-------------+-----------------+------+----------------------------------------------+------+---------+------+--------+---------------------

the possible keys are :

index_sales_on_type,
index_sales_on_buyer_id,
index_sales_on_merchand_id,
index_sales_on_type_and_country_id,
index_sales_public_recent_activity

Why doesn't MySQL use index_sales_viewed by default? Could there be a better index?

Thank you!

Upvotes: 2

Views: 3187

Answers (1)

ajreal
ajreal

Reputation: 47331

This is wrong use on NULL, please change all the column used in the index to be NOT NULL

refer this When to use NULL in MySQL tables

official documentation

If this column is NULL, there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again

Mysql chosen index index_sales_on_type_and_country_id because you are not compare with NULL value

Upvotes: 4

Related Questions