Index not used in query. How to improve performance?

Question

I have this query:

SELECT
    *
FROM
    `av_cita`
JOIN `av_cita_cstm` ON (
    (
        `av_cita`.`id` = `av_cita_cstm`.`id_c`
    )
)
WHERE
    av_cita.deleted = 0

This query takes over 120 seconds to finish, yet I have added all indexes.

When I ask for the execution plan:

explain SELECT * FROM `av_cita` 
        JOIN `av_cita_cstm` ON ( ( `av_cita`.`id` = `av_cita_cstm`.`id_c` ) ) 
        WHERE av_cita.deleted = 0;

I get this:

+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------+--------+-------------+
| id | select_type | table        | type   | possible_keys        | key     | key_len | ref                       | rows   | Extra       |
+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------+--------+-------------+
|  1 | SIMPLE      | av_cita      | ALL    | PRIMARY,delete_index | NULL    | NULL    | NULL                      | 192549 | Using where |
|  1 | SIMPLE      | av_cita_cstm | eq_ref | PRIMARY              | PRIMARY | 108     | rednacional_v2.av_cita.id |      1 |             |
+----+-------------+--------------+--------+----------------------+---------+---------+---------------------------+--------+-------------+

delete_index is listed in the possible_keys column, but the key is null, and it doesn't use the index.

Table and index definitions:

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id               | char(36)     | NO   | PRI | NULL    |       |
| name             | varchar(255) | YES  | MUL | NULL    |       |
| date_entered     | datetime     | YES  | MUL | NULL    |       |
| date_modified    | datetime     | YES  |     | NULL    |       |
| modified_user_id | char(36)     | YES  |     | NULL    |       |
| created_by       | char(36)     | YES  | MUL | NULL    |       |
| description      | text         | YES  |     | NULL    |       |
| deleted          | tinyint(1)   | YES  | MUL | 0       |       |
| assigned_user_id | char(36)     | YES  | MUL | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
+---------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name           | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| av_cita |          0 | PRIMARY            |            1 | id               | A         |      192786 |     NULL | NULL   |      | BTREE      |         |               |
| av_cita |          1 | delete_index       |            1 | deleted          | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| av_cita |          1 | name_index         |            1 | name             | A         |       96393 |     NULL | NULL   | YES  | BTREE      |         |               |
| av_cita |          1 | date_entered_index |            1 | date_entered     | A         |       96393 |     NULL | NULL   | YES  | BTREE      |         |               |
| av_cita |          1 | created_by         |            1 | created_by       | A         |         123 |     NULL | NULL   | YES  | BTREE      |         |               |
| av_cita |          1 | assigned_user_id   |            1 | assigned_user_id | A         |        1276 |     NULL | NULL   | YES  | BTREE      |         |               |
| av_cita |          1 | deleted_id         |            1 | deleted          | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| av_cita |          1 | deleted_id         |            2 | id               | A         |      192786 |     NULL | NULL   |      | BTREE      |         |               |
| av_cita |          1 | id                 |            1 | id               | A         |      192786 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+--------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

How can I improve the performance of this query?

Answer 1

The query is losing time on making the join. I would strongly suggest to create and index on av_cita_cstm.id_c. Then the plan will probably be changed to use that index for the av_cita_cstm table, which is much better than PRIMARY. As a consequence PRIMARY will be used on ac_cita.

I think that will bring a big improvement. You might still get more improvement if you make sure delete_index is defined with two fields: (deleted, id), and then move the where condition of the SQL statement into the join condition. But I am not sure MySql will see this as a possibility.

Answer 2

The index on deleted is not used probably because the optimizer has decided that a full table-scan is cheaper than using the index. MySQL tends to make this decision if the value you search for is found on about 20% or more of the rows in the table.

By analogy, think of the index at the back of a book. You can understand why common words like "the" aren't indexed. It would be easier to just read the book cover-to-cover than to flip back and forth to the index, which only tells you that "the" appears on a majority of pages.

If you think MySQL has made the wrong decision, you can make it pretend that a table-scan is more expensive than using a specific index:

SELECT
    *
FROM
    `av_cita` FORCE INDEX (deleted_index)
JOIN `av_cita_cstm` ON (
    (
        `av_cita`.`id` = `av_cita_cstm`.`id_c`
    )
)
WHERE
    av_cita.deleted = 0

Read http://dev.mysql.com/doc/refman/5.7/en/index-hints.html for more information about index hints. Don't overuse index hints, they're useful only in rare cases. Most of the time the optimizer makes the right decision.

Your EXPLAIN plan shows that your join to av_cita_cstm is already using a unique index (the clue is "type: eq_ref" and also the "rows: 1"). I don't think any new index is needed in that table.

I notice the EXPLAIN shows that the table-scan on av_cita scans about an estimated 192549 rows. I'm really surprised that this takes 120 seconds. On any reasonably powerful computer, that should run much faster.

That makes me wonder if you have something else that needs tuning or configuration on this server:

• What other processes are running on the server? A lot of applications, perhaps? Are the other processes also running slowly on this server? Do you need to increase the power of the server, or move applications onto their own server?

  If you're on MySQL 5.7, try querying the sys schema: this:

  select * from sys.innodb_buffer_stats_by_table 
  where object_name like 'av_cita%';
  

• Are there other costly SQL queries running concurrently?

• Did you under-allocate MySQL's innodb_buffer_pool_size? If it's too small, it could be furiously recycling pages in RAM as it scans your table.

  select @@innodb_buffer_pool_size;
  

• Did you over-allocate innodb_buffer_pool_size? Once I helped tune a server that was running very slowly. It turned out they had a 4GB buffer pool, but only 1GB of physical RAM. The operating system was swapping like crazy, causing everything to run slowly.

Another thought: You have shown us the columns in av_cita, but not the table structure for av_cita_cstm. Why are you fetching SELECT *? Do you really need all the columns? Are there huge BLOB/TEXT columns in the latter table? If so, it could be reading a large amount of data from disk that you don't need.

When you ask SQL questions, it would help if you run

SHOW CREATE TABLE av_cita\G
SHOW TABLE STATUS LIKE 'av_cita'\G

And also run the same commands for the other table av_cita_cstm, and include the output in your question above.