MySQL Forums
Forum List  »  InnoDB

Re: Using indexes for "IN" clauses with composite values
Posted by: Josep Blanquer
Date: April 17, 2014 12:16PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Using indexes for "IN" clauses with composite values
1815
April 17, 2014 12:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.