How do I make my MySQL Qyery with an "IN" statement faster?

Question

My query runs quite slow. I have two lare query's on my page. It has become running slow since I have insert an "in" statement.

    SELECT field1, field2, field3, field4, field5, field6, 
           DATE_FORMAT(field7, '%d-%m') AS field7_1, 
           DATE_FORMAT(fjield8, '%d-%m') AS field8_1 
    FROM 
         table1
    where 
       field9 in (select field1 from table2 where id = '$id_session') OR
       field10 in (select field1 from table2 where id = '$id_session')
    order by 
       field7_1 desc
   Limit 
       50, 100";

I think the problem is the part "where field9 in (select field1 from table2 where id = '$id_session')" It is searching in another table for values that have to be in the where clause.

It is taking about 8 sec to load which is to slow. I have reduced the number of records in the database, but that does it not make faster.

Please help.

you need to specify the relation between table1 and table2 to modify the query adding a JOIN statement. — Ghigo, May 22 '13 at 08:29

score 2 · Answer 1 · answered May 22 '13 at 08:18

2

You can usually refactor queries using IN to use JOIN instead.

answered May 22 '13 at 08:18

Autio

327
2
12

boisvert · Answer 2 · 2013-05-22T14:03:45.100

Your query with a join:

SELECT field1, field2, field3, field4, field5, field6, 
       DATE_FORMAT(field7, '%d-%m') AS field7_1, 
       DATE_FORMAT(fjield8, '%d-%m') AS field8_1 
FROM table1 INNER JOIN table2
ON table1.field9 = table2.field1 OR
   table1.field10 =  table2.field1
where table2.id = '$id_session'
order by field7_1 desc
Limit 50, 100;

Or so I think, it's not tested. You may have to disambiguate fields - put table1. in front of them. Indexing - table2.id is indexed already; indexing the joined columns - table1 fields 9 and 10, and table2.field1, might help. But indexes slow down writes, so only use the ones that make a real difference.

The way you filter and sort a large amount of data to then ask for only the second batch of 50 is also a factor in the slow response. If there is some way you can obtain the same result as limit, but by operating on the date field (e.g. where last week's records) it may be a lot better.

Oops! Typo, now corrected. To get an equivalent to the "In (select ...)" we have to match table1.field9 to table2.field1. Thank you @Ghigo. — boisvert, May 22 '13 at 11:42

score 1 · Answer 3 · edited May 23 '17 at 11:43

1

See my answer to similar question for background here, and why JOINs will be much faster than subqueries:

MySQL "IN" queries terribly slow with subquery but fast with explicit values

edited May 23 '17 at 11:43

Community

1
1

answered May 22 '13 at 11:44

Brian

6,391
3
33
49

score 0 · Answer 4 · answered May 22 '13 at 08:26

0

you can join two tables by doing something like this :

SELECT table1.field1, table1.field2, ..., table2.field1 FROM table1, table2 WHERE [your conditions]

join works faster

answered May 22 '13 at 08:26

Ala Alam Falaki

345
1
9
19

where is join ? this is join ... you don't have to write "INNER JOIN" or "LEFT JOIN" or ... in the query , you write something like this, it will work just fine . – Ala Alam Falaki May 22 '13 at 08:36
"write something like this, it will work just fine" - my colleagues and I have a special category for this kind of answer... We call it "more hand-waving than coding" – boisvert May 22 '13 at 12:23
@boisvert: you mean it's necessary to write JOIN in code ? It's faster that way ? – Ala Alam Falaki May 22 '13 at 16:20
@AlaaA.F. It's possible to use a where statement instead of join code. But you have to get specific: which columns are made equal, etc. – boisvert May 22 '13 at 16:38
@boisvert : your right, i assume that's obvious, now i think i'm wrong ... thanks for your advice, i try to be more clear from now on :) – Ala Alam Falaki May 22 '13 at 16:41
@AlaaA.F. having said that, I tried to do just that by answering this question with a complete SQL query, but nobody seems to think the answer is any good, so I'm not an example to follow :) – boisvert May 22 '13 at 16:46
@boisvert: are u kidding ? your answer was complete !! i'm going to upvote your answer right now :) – Ala Alam Falaki May 22 '13 at 16:57
@AlaaA.F. oops, thanks. I think the reason why the answer wasn't voted up before was that there was an error in it at first. – boisvert May 22 '13 at 21:20
@boisvert: maybe it's for that :) – Ala Alam Falaki May 23 '13 at 11:32

How do I make my MySQL Qyery with an "IN" statement faster?

4 Answers4