CODEWITHSUNDEEP
mysql
PaulLing
PaulLing

Reputation: 2150

Combining two fast queries results in extremely slow query

Query 1:

SELECT  a.name,a.id
FROM name_list AS a 
WHERE   a.created_time BETWEEN "2013-03-14 00:00:00" AND "2013-03-14 23:59:59"

Query 2:

SELECT address_nr
FROM address_list
WHERE user_id="1117770"
LIMIT 1

To execute the queries 1 and 2, i just need 0.003 second for both. But when i combine them and make the 2nd query as a subquery, as below:

SELECT  a.name,a.id,(SELECT address_nr FROM address_list WHERE user_id=a.id LIMIT 1) as 'address'
    FROM name_list AS a 
    WHERE   a.created_time BETWEEN "2013-03-14 00:00:00" AND "2013-03-14 23:59:59"

I need 1 second to execute it. When i am retrieving thousands of data, it needed thousand seconds.

Can someone please guide me in this? Thanks!

Upvotes: 0

Views: 77

Answers (1)

Sean Johnson
Sean Johnson

Reputation: 5607

Instead of using a subquery, try a join. This should be markedly faster:

SELECT a.name,a.id,b.address_nr address
    FROM name_list a
    LEFT JOIN address_list b ON a.id=b.user_id
    WHERE a.created_time BETWEEN "2013-03-14 00:00:00" AND "2013-03-14 23:59:59"

The reason your subquery is taking so long is because it isn't just running two queries, it's running the one main query and the subquery for every single row in the resultset from the main query. If table a has 1000 rows, you're running 1001 queries.

Upvotes: 2

Related Questions