CODEWITHSUNDEEP
mysqloptimizationlimitunion
Redrif
Redrif

Reputation: 650

Mysql union optimization with subquery

My problem is this query: 

(SELECT    
     ID1,ID2,ID3,ID4,ID5,ID10,ID11,ID13,ID14,ID454,ID453,
     TIME,TEMP_ID,'ID_AUTO',PREDAJCA,VYTVORIL,MAIL,TEMP_ID_HASH,ID_SEND 
   FROM `load_send_calc` 
   WHERE `TEMP_ID` LIKE '$find%' 
     AND ACTIVE = 1 AND TEMP_ID > 0)
UNION ALL
(SELECT 
     ID1,ID2,ID3,ID4,ID5,ID10,ID11,ID13,ID14,ID454,ID453,TIME,'',
     ID_AUTO,'','','','','' 
  FROM `temp` 
  WHERE `ID_AUTO` LIKE '$find%' 
    AND `ID_AUTO` NOT IN (SELECT TEMP_ID 
                            FROM `load_send_calc` 
                            WHERE `load_send_calc`.ACTIVE = 1)
)
ORDER BY TIME  DESC LIMIT $limitFrom,$limitTo;

There are 18000 records in table load_send_calc and 3000 table temp. The query itself take more than 2 minutes to execute. Is there any way to optimize this time?

I already tried to put order into each subqueries but it didnt help significantly. I am really desperate so I really appreciate any kind of help.

EDIT: Here is EXPLAIN result : 

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY load_send_calc  ALL NULL    NULL    NULL    NULL    18394   Using where
2   UNION   temp    ALL NULL    NULL    NULL    NULL    1918    Using where
3   DEPENDENT SUBQUERY  load_send_calc  ALL NULL    NULL    NULL    NULL    18394   Using where
NULL    UNION RESULT    <union1,2>  ALL NULL    NULL    NULL    NULL    NULL    Using filesort

Upvotes: 2

Views: 267

Answers (2)

Rick James
Rick James

Reputation: 142453

3 things to speed it up:

INDEX(active, temp_id) -- significantly better than two separate indexes

IN ( SELECT ... ) performs poorly, especially in old versions of MySQL. Turn it into a JOIN.

Add a LIMIT to each SELECT. For example:

( SELECT ... ORDER BY ... LIMIT 80 )
UNION ALL
( SELECT ... ORDER BY ... LIMIT 80 )
             ORDER BY ... LIMIT 70, 10;

The inner ones have a limit of the max needed -- the outer's offset + limit.

Upvotes: 2

pala_
pala_

Reputation: 9010

Thanks for adding your explain output - it tells us a lot. The query isn't using a single index, which is very bad for performance. A very simple optimisation would be add indexes on the fields that are used in the join, and also in the where clauses. In your case those fields would be:

load_send_calc.temp_id
load_send_calc.active
temp.id_auto

In addition to these, you have an unnecessary AND TEMP_ID > 0, since you are already limiting on the same field with WHERE TEMP_ID LIKE '$find%'

Upvotes: 2

Related Questions