SELECT * FROM table WHERE id IN (....) too slow

[phdphd]

Hi All,

 

I use a query that gets rows according to specified ids. The typical syntax of the query is as follows:

 

SELECT * FROM table WHERE id IN(1,2,3,....,100,...)

The query is very slow (more than 10 seconds) if the IN clause contains thousands of ids. (The same query without the IN clause runs instantaneously.)

 

Note that the number of ids is random and depends of choices from the user.

I read here and there than using an "inner join" might be a solution. I would need some help in converting this query into an inner join-based query. Thanks a lot.

[gristoi]

firstly your query is only querying 1 table, so what would you join? have you also tried adding an index to the id table, this will speed the query dramatically

[phdphd]

An index is already created. As for "self-joining", this approach seems suggested here :

 

http://stackoverflow.com/questions/10023117/select-where-in-subquery-slow

[mac_gyver]

the link you posted was for a query with a sub-query being changed to a self join. that's not what you are doing, based on your example query. if you are actually first selecting these ids from that same table, you would need to state or show this information before someone could possibly help you with the solution.

[phdphd]

Let me rephrase the context of my issue. I have a PHP processing that ends with the building of a series of ids. Then there is a SQL query based on this series of ids, that I run against a table with an Id column, in order to get all the data in rows matching the ids provided.

[kicken]

For such a simplistic query, the only things really that would improve it is would be

 

1) Ensure an index exists on the id column

2) If the table is massive, partitioning may help

 

Add EXPLAIN to the font of the query and run it to verify that your index is being used.

[phdphd]

I noticed that PhpMyAdmin reports a processing time of 0.0246 sec. But actually it displays the first set of rows only after more or less 15 seconds.

The Explain reports the following :

id   select_type  table       type possible_keys key    key_len ref     rows  Extra

1    SIMPLE      ma_table ALL PRIMARY        NULL NULL    NULL 8211 Using where

 

(If I run the same query without the IN clause, both the processing and the rendering are immediate.)

Edited July 3, 2013 by phdphd

[kicken]

Your EXPLAIN output seems to indicate that no indexes are being used. Is your ID column the PRIMARY KEY? Is it the only column in the primary key, or are there others?

[phdphd]

Yes, the ID column is the primary key, and it is the only one there.

[Barand]

How are you getting the list of ids for selection in the first instance?