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:
More indexes will need more memory and storage (given a billion rows quite a bit), so that's a concern.
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 ?