1

I've a question for an expert in how innodb indexes are structured (mysql 8.0.18)

Say I have 4 varchar columns on a table with one billion rows:

country, state, city, attraction

I have queries that look for all or certain attractions, either by country, state, city or attraction name.

query1: "select * from table where attraction like 'asd%' and country = 'X'"  
query2: "select * from table where attraction like 'asd%' and country = 'X' and state = 'Y' and city = 'Z'"    
query3: "select distinct attraction from table where country='X'"     
query4: "select distinct attraction from table where attraction like 'Ux%'



Combined index: (attraction, country, state, city)  

This index would cover all 4 queries.

Can I expect similar performance on query1,3,4 in comparison to a specialized index ?

Specialized index1:  (attraction, country)  
Specialized index2:  (attraction)  

I don't have the time to dive into the details of innodb storage, I hope someone did that already ;)

My main thoughts on this:

  1. More indexes will need more memory and storage (given a billion rows quite a bit), so that's a concern.

  2. If an index that's made for 4 columns is called on a query that only needs one (the first) column or on two (the first two) columns is the data access sequential and as effective as when having small dedicated indexes (that basically contain duplicate data)?

So should I have one index, covering the WHERE requirements of all 4 queries or 3 indexes, each dedicated to the query it serves ?

0

2 Answers 2

2

danblack's post answers the main question about the best index strategy for you queries.

However, I would add a sometimes forgotten optimization of the index strategy, which is implemented in most recent versions of RDBMs (MySQL, MariaDB, PostgreSQL...): covering indexes

Definition of covering index: (from MySQL documentation)

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O

That means that your third query:

select distinct attraction from table where country='X'

would benefit more from a (country, attraction) index than a simple (country) index.

1
  • good addition. Thank you.
    – danblack
    Jan 29, 2020 at 19:52
1

MySQL reference (country=X) elements should be before range (attraction like 'asd%') in an index.

To cover query4 a single attraction index is needed.

An index that covers query1 and query2 would be (country, attraction).

An ideal query3 index would be (country, state, city, attraction), however if the country, attraction query sufficiently narrows the search (country, attraction, state, city) can be used with reasonable efficiency, it will narrow to the country and range search the attraction criteria and filter basted on state/city as it goes through this range. This a convenient extension to the query1/query2 optimized index.

Based on this I recommend:

  • index (attraction)
  • index (country, attraction, state, city)
3
  • Thanks for your response. Let me re-phrase my question: index (attraction) index (attraction, country) Are both of those indexes equally performant for a query that looks for 'WHERE attraction="X" ?' The core question I wanted to ask is if an index is as performant when it has additional (unneeded) columns or if the additional unrequired columns would slow queries down that don't need them
    – John
    Jan 29, 2020 at 4:14
  • Appending unused elements to an index makes the index larger, so it might have a few more pages in it and therefore be a little slower to retrieve of disk and storage in innodb buffer pool is marginally larger, however for small length character fields (<100) or numeric fields it is unlikely to make any noticeable difference with the query retrieval time.
    – danblack
    Jan 29, 2020 at 4:43
  • Note the order of the index elements is important (more info)
    – danblack
    Jan 29, 2020 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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