MariaDB Index – Correct Index Not Being Used

indexmariadb

Fiddle: https://www.db-fiddle.com/f/m4vsq4ERyBhiBNdZqALmVP/0

I have two indexes, one for some_other_id and one forcreated_at , some_other_id.

The select query uses the first index on production server (~23M rows) while it uses the second one on my dev machine (1 row).

Server explain:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: symbol_details
         type: ref
possible_keys: symbol_details_some_other_id_index,symbol_details_created_at_some_other_id_index
          key: symbol_details_some_other_id_index
      key_len: 8
          ref: const
         rows: 24152
        Extra: Using where

dev explain:

           id: 1
  select_type: SIMPLE
        table: symbol_details
         type: range
possible_keys: symbol_details_some_other_index,symbol_details_created_at_some_other_id_index
          key: symbol_details_created_at_some_other_id_index
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using where; Using index

Also in fiddle it is not using the second index, while clearly having limits on created_at.

Is is because of the number of rows?

I want to use such queries in production, but this way it is way too slow. A single query like this returns ~100 rows out of ~23M rows, and I thought the second index would cover this query, but it seems it somehow does not.

Any idea on how to use index, or created another index for this query, or modify query to use index.

The best solution would be to not touch the query, since it is generated by ORM. Using hints in query while using ORM is not exactly clean, but doable if it is the only choice.

Thanks!

Dev Mariadb version 10.4.13 and production is 10.4.12 (running in docker btw).

EDIT:

The first index is being used somewhere else, and it needs to be there (index on some_other_id). Maybe I can merge the two indexes into on? IDK yet.

EDIT2 why I chose to have two index:

Since some_other_id's cardinality is ~70k and created_at is ~400k I chose have second index to filter created_at first. Now I'm thinking if I was right to think this way or not.

Best Answer

select  count(*)
    from  symbol_details
    where  tsetmc_id = 41974758296041288
      and  created_at >  '2020-06-20'
      and  created_at <  '2020-06-31';

Optimal index, in this order:

INDEX(tsetmc_id, created_at)

When you started with the range value, it could not go past that to the 'id'. The simple rule is: Put the = columns first in the index definition. More: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Further notes

  • The optimizer may decide that a table scan is faster than bothering with the INDEX. This usually happens when it appears that more than about 20% of the table needs to be touched. (Using the index implies bouncing back and forth between the index's BTree and the data's BTree.) If it shuns the index, don't worry; it is probably a wise decision.

  • The order of the columns in the index is critical when a "range" is involved. The Optimizer will stop considering columns once it gets past the "range".

Related Question