Re: Using indexes for "IN" clauses with composite values
Rick,
In this particular case, I need to build tools that do "muti-gets" on existing tables. I already have the "ids" from elsewhere, and I just need to retrieve those rows in batches. No joins possible.
So, there are cases where the "ids" correspond to a single field in a table. In these cases using: SELECT * FROM TABLE WHERE `field_name` IN ('a','b','c') works well. That is, MySQL uses the index on field_name to traverse the search.
However, there are cases that the table does not have a unique single-column id, and I need to use compound set of columns to fully define the row I want. Also, it happens to be that often , that one of these columns has an index, so I was expecting that MySQL would use it in its traversal. ( The tables I'm dealing with are already there, I'm just the consumer, so changing their definition, PKs...is not an option).
For example, there is a table with:
resource_uid varchar(255)
tenant_id bigint(20) unsigned
parent_id bigint(20) unsigned
for which there's an index on resource_uid (with very high selectivity)
so I was expecting that doing queries with:
SELECT * from TABLE WHERE (`resource_uid`,`tenant_id`,`parent_id`) IN (('aaa',1,1), ('bbb',1,1), ('ccc',2,1) ....long list...)
would use the resource_uid index. But it does not.
I then added a composite index to the table on (resource_uid,tenant_id,parent_id) and was even more surprised when even then, the select was still doing a table scan.
Is there a way to perform that query in any other way that it is performant I don't know about? Or is the solution to somehow jump to MySQL > 5.7.3?
NOTE: for now, it is not possible for me to jump to that version, so I am currently settling on adding yet another condition to the where clause, that does an IN clause with a single column that it is indexed. Like this:
SELECT * from TABLE WHERE
(`resource_uid`,`tenant_id`,`parent_id`) IN (('aaa',1,1), ('bbb',1,1), ('ccc',2,1) ....long list...)
AND
`resource_uid` IN ('aaa','bbb','ccc' ... long list...)
not pretty, but it seems to perform reasonably well.
Thoughts?