0

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.

  • you often have to Force INDEX the rdms to select a better index. – nbk Jun 30 at 14:38
  • @nbk I didn't think DBs would be this dumb. This is 2020 and we're still trying to force RDBMs to use indexes? Anyway, any explanation or thought on why index is not picked? – vfsoraki Jun 30 at 18:31
  • in the end it is an algorithm that decides which selects one,that is with one or two not bad, but in the end effect it is only a guess which would better, and that kind of heuristics often fails. You also have to choose one to force the rdms to take it, and also there you would test different ones in hope to find one good. as the table grow bigger and the query more complex it gets more and more difficult to decide for one and the rdms don't has the luxury ti test them all – nbk Jun 30 at 20:47
  • The number of rows definitely affects what decision the optimizer makes. – Lennart Jul 1 at 19:00
1
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".

| improve this answer | |
  • Seems like this was the way to go. I still don't get those indexes. Having index on both fields in query, should result in engine using that index. Just reversing order of columns in index should not have a notable difference. Anyway, thanks! – vfsoraki Jul 6 at 9:28
  • @vfsoraki - I added to my Answer. – Rick James Jul 6 at 16:01
  • Thanks! I will remember your second point from now on :thumsup: – vfsoraki Jul 6 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.