How to write effective "SELECT a FROM t WHERE b IN() ORDER BY a" query?

Question

I have a simple query that is way too slow considering my needs. The query is:

SELECT a FROM tableA WHERE b IN ("SOME_IDS_LIST") ORDER BY a

tableA has about 300 000 rows and contains index (a,b). The longer "SOME_IDS_LIST" list the slower the query. Is there any way of speeding up this kind of query? I was looking for some generic solution over the internet but with no luck.

@HamletHakobyan Only by a? Why not (a,b) to allow DB to use covering index? — Jacek Francuz, Aug 02 '13 at 14:41
You may want to review the column order in your index. See this question http://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes — Declan_K, Aug 02 '13 at 14:41
If B is the column you you more often in the WHERE clause, it should be the first column in the index. — Declan_K, Aug 02 '13 at 14:42
Would it help to sort the two tables `code`a`code` and `code`b`code`, then use some more code to compare the first two elements continuously - if they match, you store, else you continue with the the next value — aashnisshah, Aug 02 '13 at 14:44
I suggest for whatever query you're using for SOME_IDS_LIST, create an index on the elements in the where clause of that query. — dseibert, Aug 02 '13 at 14:48
@dseibert - Ok but by doing this the query is even slower because it goes through all b rows and then needs to sort them. — Jacek Francuz, Aug 02 '13 at 14:50

score 1 · Accepted Answer · answered Aug 02 '13 at 14:42

1

put on yout table an index on b field.

If the result is too slow.

Instead use IN clause, create a temporary table with your ID ad link your temporary table with your tableA using INNER JOIN statement.

Your script:

CREATE TEMPORARY TABLE myTempTable (id int)
INSERT INTO myTempTable (all your ID)

SELECT *
FROM tableA
JOIN myTempTable
ON tableA.b = myTempTable.id

About creation of temporary table see here

answered Aug 02 '13 at 14:42

Joe Taras

15,166
7
42
55

Ok, but the problem is that i HAVE TO use ORDER BY a. I don't think it solves it. – Jacek Francuz Aug 02 '13 at 14:49
@FlexJack: Did you *try* an index on "b"? – Mike Sherrill 'Cat Recall' Aug 02 '13 at 14:50
@MikeSherrill'Catcall' - Yes, it actually makes the query even slower because of the ORDER BY clause. DB needs to go through all the results and sort them. The more rows the worse. – Jacek Francuz Aug 02 '13 at 14:51
@JoeTaras Ok, thanks. I will give it a try. I need to change my code a bit to achieve it. I will let you know how it works. – Jacek Francuz Aug 02 '13 at 15:04

score 1 · Answer 2 · answered Aug 02 '13 at 14:58

1

It would be worthwhile to test an EXISTS clause. These can often have significant performance boosts over an IN clause. Again this would require a temp table

CREATE TEMPORARY TABLE myTempTable (id int)
INSERT INTO myTempTable (all your ID)

SELECT  TA.a 
FROM    tableA TA
WHERE   EXISTS 
        (SELECT *
        FROM    myTempTable TT
        WHERE   TT.id = TA.b
        )
ORDER BY
        TA.a

answered Aug 02 '13 at 14:58

Declan_K

6,726
2
19
30

Ok, thanks. I will give it a try. I need to change my code a bit to achieve it. I will let you know how it works. – Jacek Francuz Aug 02 '13 at 15:04

How to write effective "SELECT a FROM t WHERE b IN() ORDER BY a" query?

2 Answers2