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

i working old legacy project , have problem slow query.

i have following database schema:

table: search_api_db_full_index_text rows : 1612226 +------------+------------------+------+-----+---------+-------+ | field      | type             | null | key | default | | +------------+------------------+------+-----+---------+-------+ | 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     |         |       42323 |     null | null   |      | btree      |         |               | | search_api_db_full_index_text |          0 | primary    |            2 | field_name  |         |      134023 |     null | null   |      | btree      |         |               | | search_api_db_full_index_text |          0 | primary    |            3 | word        |         |     1608286 |     null | null   |      | btree      |         |               | | search_api_db_full_index_text |          1 | word_field |            1 | word        |         |      402071 |       20 | null   |      | btree      |         |               | | search_api_db_full_index_text |          1 | word_field |            2 | field_name  |         |      229755 |     null | null   |      | btree      |         |               | +-------------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+    table: search_api_db_full_index_field_event_date_mutli_field_date_opt rows: 100421     +---------+------------+------+-----+---------+-------+ | field   | type       | null | key | default | | +---------+------------+------+-----+---------+-------+ | 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     |         |       50380 |     null | null   |      | btree      |         |               | | search_api_db_full_index_field_event_date_mutli_field_date_opt |          0 | primary  |            2 | value       |         |      100760 |     null | null   |      | btree      |         |               | | search_api_db_full_index_field_event_date_mutli_field_date_opt |          1 | value    |            1 | value       |         |      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 | | +---------+------------+------+-----+---------+-------+ | item_id | bigint(20) | no   | pri | null    |       | | value   | bigint(20) | no   | pri | null    |       | +---------+------------+------+-----+---------+-------+  indexes 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     |         |       50160 |     null | null   |      | btree      |         |               | | search_api_db_full_index_field_event_date_mutli_field_date_o_1 |          0 | primary  |            2 | value       |         |      100320 |     null | null   |      | btree      |         |               | | search_api_db_full_index_field_event_date_mutli_field_date_o_1 |          1 | value    |            1 | value       |         |      100320 |     null | null   |      | btree      |         |               | +----------------------------------------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 

currently, query below takes around 170 seconds execute.

it's odd because in past never slow. ( 10 - 12 seconds )

that query generated drupal view, without left joins query runs quickly. ideally, fix adding indexes. possible?

select sql_no_cache      t.item_id item_id,     sum(score) score,     t.word '%test%' w0     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     ((t.word '%test%' escape '\\'))         , (field_name in ('body:value' , 'field_event_organiser:title',         'field_event_place:title',         'field_image_caption',         'title'))         , (((t_2.value >= '1474502400')         , (t_3.value <= '1537660799'))) group t.item_id , item_id , w0 order score desc;   output explain:     +------+-------------+-------+-------+---------------+---------+---------+-----------------------------------+-------+-----------------------------------------------------------+ | id   | select_type | table | type  | possible_keys | key     | key_len | ref                               | rows  |                                                     | +------+-------------+-------+-------+---------------+---------+---------+-----------------------------------+-------+-----------------------------------------------------------+ |    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                                               | +------+-------------+-------+-------+---------------+---------+---------+-----------------------------------+-------+-----------------------------------------------------------+ 

edit:

if force indexes query execution time going down 170-240 seconds 30-50 seconds. it's still kind of slow.

  select sql_no_cache     t.item_id item_id,     sum(score) score,     t.word '%test%' w0     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     ((t.word '%test%' escape '\\'))         , (field_name in ('body:value' , 'field_event_organiser:title',         'field_event_place:title',         'field_image_caption',         'title'))         , (((t_2.value >= '1474502400')         , (t_3.value <= '1537660799'))) group t.item_id , item_id , w0 order score desc; 

when force index on search_api_db_full_index_text word_field query works should ( less 1 second )

 select sql_no_cache     t.item_id item_id,     sum(score) score,     t.word '%test%' w0     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     ((t.word '%test%' escape '\\'))         , (field_name in ('body:value' , 'field_event_organiser:title',         'field_event_place:title',         'field_image_caption',         'title'))         , (((t_2.value >= '1474502400')         , (t_3.value <= '1537660799'))) group t.item_id , item_id , w0 order score desc; 

the issue joining of search_api_db_full_index_field_event_date_mutli_field_date_opt table. table's alias t_2 , in column of first row of explain results see: using where; using index; using temporary; using filesort

using temporary, using filesort quite bad performance point of view. key column can see mysql decided use value index, supports where clause, not support join.

since primary key in table covers both item_id , value fields, try force mysql use primary index when joining t_2 viaforce index index hint. apparently, mysql optimiser makes wrong decision index use in query.