Can I improve this query by adding new indexes, or how to improve that query?

Question

I am working with an old legacy project and I have a problem with a very slow query.

I have the following database schema:

table: search_api_db_full_index_text
rows : 1612226
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| item_id    | bigint(20)       | NO   | PRI | NULL    |       |
| field_name | varchar(255)     | NO   | PRI | NULL    |       |
| word       | varchar(50)      | NO   | PRI | NULL    |       |
| score      | int(10) unsigned | NO   |     | 0       |       |
+------------+------------------+------+-----+---------+-------+

indexes for: search_api_db_full_index_text
+-------------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                         | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| search_api_db_full_index_text |          0 | PRIMARY    |            1 | item_id     | A         |       42323 |     NULL | NULL   |      | BTREE      |         |               |
| search_api_db_full_index_text |          0 | PRIMARY    |            2 | field_name  | A         |      134023 |     NULL | NULL   |      | BTREE      |         |               |
| search_api_db_full_index_text |          0 | PRIMARY    |            3 | word        | A         |     1608286 |     NULL | NULL   |      | BTREE      |         |               |
| search_api_db_full_index_text |          1 | word_field |            1 | word        | A         |      402071 |       20 | NULL   |      | BTREE      |         |               |
| search_api_db_full_index_text |          1 | word_field |            2 | field_name  | A         |      229755 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+



table: search_api_db_full_index_field_event_date_mutli_field_date_opt
rows: 100421    
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| item_id | bigint(20) | NO   | PRI | NULL    |       |
| value   | bigint(20) | NO   | PRI | NULL    |       |
+---------+------------+------+-----+---------+-------+

indexes for: search_api_db_full_index_field_event_date_mutli_field_date_opt
+----------------------------------------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                                                          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------------------------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| search_api_db_full_index_field_event_date_mutli_field_date_opt |          0 | PRIMARY  |            1 | item_id     | A         |       50380 |     NULL | NULL   |      | BTREE      |         |               |
| search_api_db_full_index_field_event_date_mutli_field_date_opt |          0 | PRIMARY  |            2 | value       | A         |      100760 |     NULL | NULL   |      | BTREE      |         |               |
| search_api_db_full_index_field_event_date_mutli_field_date_opt |          1 | value    |            1 | value       | A         |      100760 |     NULL | NULL   |      | BTREE      |         |               |
+----------------------------------------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+



table: search_api_db_full_index_field_event_date_mutli_field_date_o_1
rows: 100099   
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| item_id | bigint(20) | NO   | PRI | NULL    |       |
| value   | bigint(20) | NO   | PRI | NULL    |       |
+---------+------------+------+-----+---------+-------+

indexes for search_api_db_full_index_field_event_date_mutli_field_date_o_1
+----------------------------------------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                                                          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------------------------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| search_api_db_full_index_field_event_date_mutli_field_date_o_1 |          0 | PRIMARY  |            1 | item_id     | A         |       50160 |     NULL | NULL   |      | BTREE      |         |               |
| search_api_db_full_index_field_event_date_mutli_field_date_o_1 |          0 | PRIMARY  |            2 | value       | A         |      100320 |     NULL | NULL   |      | BTREE      |         |               |
| search_api_db_full_index_field_event_date_mutli_field_date_o_1 |          1 | value    |            1 | value       | A         |      100320 |     NULL | NULL   |      | BTREE      |         |               |
+----------------------------------------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Currently, the query below takes around 170 seconds to execute.

It's odd because in the past it never was as slow. ( mostly 10 - 12 seconds )

That query is generated by the Drupal view, without left joins that query runs very quickly. Ideally, I would like to fix that by adding extra indexes. Is it possible?

SELECT SQL_NO_CACHE 
    t.item_id AS item_id,
    SUM(score) AS score,
    t.word LIKE '%test%' AS w0
FROM
    search_api_db_full_index_text t
        LEFT OUTER JOIN
    search_api_db_full_index_field_event_date_mutli_field_date_opt t_2 ON t.item_id = t_2.item_id
        LEFT OUTER JOIN
    search_api_db_full_index_field_event_date_mutli_field_date_o_1 t_3 ON t.item_id = t_3.item_id
WHERE
    ((t.word LIKE '%test%' ESCAPE '\\'))
        AND (field_name IN ('body:value' , 'field_event_organiser:title',
        'field_event_place:title',
        'field_image_caption',
        'title'))
        AND (((t_2.value >= '1474502400')
        AND (t_3.value <= '1537660799')))
GROUP BY t.item_id , item_id , w0
ORDER BY score DESC;


Output from explain:    
+------+-------------+-------+-------+---------------+---------+---------+-----------------------------------+-------+-----------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref                               | rows  | Extra                                                     |
+------+-------------+-------+-------+---------------+---------+---------+-----------------------------------+-------+-----------------------------------------------------------+
|    1 | SIMPLE      | t_2   | range | PRIMARY,value | value   | 8       | NULL                              | 35410 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | t_3   | ref   | PRIMARY,value | PRIMARY | 8       | drupal7_.t_2.item_id              |     2 | Using where; Using index                                  |
|    1 | SIMPLE      | t     | ref   | PRIMARY       | PRIMARY | 8       | drupal7_.t_2.item_id              |    38 | Using where                                               |
+------+-------------+-------+-------+---------------+---------+---------+-----------------------------------+-------+-----------------------------------------------------------+

edit:

If I force indexes the query execution time is going down from 170-240 seconds to 30-50 seconds. But it's still kind of slow.

  SELECT SQL_NO_CACHE
    t.item_id AS item_id,
    SUM(score) AS score,
    t.word LIKE '%test%' AS w0
FROM
    search_api_db_full_index_text t
        LEFT OUTER JOIN
    search_api_db_full_index_field_event_date_mutli_field_date_opt t_2  FORCE INDEX (PRIMARY) ON t.item_id = t_2.item_id 
        LEFT OUTER JOIN
    search_api_db_full_index_field_event_date_mutli_field_date_o_1 t_3  FORCE INDEX (PRIMARY) ON t.item_id = t_3.item_id
WHERE
    ((t.word LIKE '%test%' ESCAPE '\\'))
        AND (field_name IN ('body:value' , 'field_event_organiser:title',
        'field_event_place:title',
        'field_image_caption',
        'title'))
        AND (((t_2.value >= '1474502400')
        AND (t_3.value <= '1537660799')))
GROUP BY t.item_id , item_id , w0
ORDER BY score DESC;

When I also force index on search_api_db_full_index_text to word_field the query works as it should ( less than 1 second )

 SELECT SQL_NO_CACHE
    t.item_id AS item_id,
    SUM(score) AS score,
    t.word LIKE '%test%' AS w0
FROM
    search_api_db_full_index_text t FORCE INDEX (word_field)
        LEFT OUTER JOIN
    search_api_db_full_index_field_event_date_mutli_field_date_opt t_2  FORCE INDEX (PRIMARY) ON t.item_id = t_2.item_id 
        LEFT OUTER JOIN
    search_api_db_full_index_field_event_date_mutli_field_date_o_1 t_3  FORCE INDEX (PRIMARY) ON t.item_id = t_3.item_id
WHERE
    ((t.word LIKE '%test%' ESCAPE '\\'))
        AND (field_name IN ('body:value' , 'field_event_organiser:title',
        'field_event_place:title',
        'field_image_caption',
        'title'))
        AND (((t_2.value >= '1474502400')
        AND (t_3.value <= '1537660799')))
GROUP BY t.item_id , item_id , w0
ORDER BY score DESC;

@Shadow added query and 'explain' output without joins. I have index for column value with the name `value` in both tables search_api_db_full_index_field_event_date_mutli_field_date_opt, search_api_db_full_index_field_event_date_mutli_field_date_o_1 — Pawel Dubiel, Sep 22 '16 at 16:36
Sorry, realised after asking for the explain without the joins that the issue is with one of the tables added in the join, not with the table on the left hand side of the join. See my updated answer below. — Shadow, Sep 22 '16 at 16:38

Shadow · Accepted Answer · 2016-09-22T16:41:41.340

1

The issue is with the joining of search_api_db_full_index_field_event_date_mutli_field_date_opt table. The table's alias is t_2 and in the extra column of the first row of the explain results you see: Using where; Using index; Using temporary; Using filesort

Using temporary, using filesort are quite bad from performance point of view. From the key column you can see that MySQL decided to use the value index, that supports the where clause, but does not support the join.

Since your primary key in this table covers both item_id and value fields, I would try to force MySQL to use the primary index when joining t_2 viaforce index index hint. Apparently, MySQL optimiser makes a wrong decision as to which index to use in this query.

edited Sep 22 '16 at 16:41

answered Sep 22 '16 at 16:35

Shadow

33,525
10
51
64

Do you think it's possible that mysql changed optimisation path due increased number of rows? This query was slow but never so slow.. I wonder if I can just increase some mysql settings like maybe join_buffer_size etc... – Pawel Dubiel Sep 22 '16 at 16:52
1

It is absolutely possible that MySQL changed its view on which index to use. Index statistics are based on sampling. Check out the cardinality of the indexes and you may also try to force MySQL to update them. There are questions around these topics here on SO – Shadow Sep 22 '16 at 17:25
1

See http://stackoverflow.com/questions/34851674/can-cardinality-differ-for-duplicate-indexes-in-mysql or http://stackoverflow.com/questions/38302435/mysql-table-index-cardinality – Shadow Sep 22 '16 at 17:27
Thanks! I learnt a lot from this. I forced index for both joins. but the query was still kind of slow ( see my edits ) when I forced index on the main table to 'word_field' it started working as it should. ( interesting it that word_field index doesn't have item_id column included ) – Pawel Dubiel Sep 22 '16 at 19:39

Can I improve this query by adding new indexes, or how to improve that query?

1 Answers1