1

I have to 2 table, the first is a MEMORY table with 3.000.000 record the second is at the moment empty. The structure of these table is identical, both have a int(11) primary key, the two table have all primary key field different (the same key is not possible in both tables)

If I query the first table I have an answer in 0.1 seconds, the query on second table is immediate 0.01 seconds. But merging the 2 with the UNION ALL is dramatically slow (1 min 21 sec).

SELECT * FROM memtable1 LIMIT 1000 

(0.1 sec.)

SELECT * FROM table2 LIMIT 1000 

(0.01 sec.)

SELECT * FROM memtable1 
UNION ALL 
SELECT * FROM table2 LIMIT 1000 

(1 min 21 sec.)

How can I improve the speed of the query?

  • The UNION ALL query asks for all the rows from memtable1, without any limit. Try this, if you want only 1000 rows from each table: (SELECT * FROM memtable1 LIMIT 1000) UNION ALL (SELECT * FROM table2 LIMIT 1000) ; – ypercubeᵀᴹ Jul 26 '17 at 7:28
  • @ybercubeTM, my thought too. But I did a small test with two rows in t1 and 1 row in t2, did the OP's query and got 1 row. Does it build a temp table with all rows from t1 and t2 and then applies limit to that? – Lennart Jul 26 '17 at 7:32
  • @Lennart you are right, I was misinterpreting the query. Edited my answer. – ypercubeᵀᴹ Jul 26 '17 at 7:40
  • Thanks to all, your answer have been very useful to clarify ideas on UNION ALL internal mechanisms. To all effects I have a more complex situation, I will open a new question. – Zeno Jul 26 '17 at 8:20
6

Your query is equivalent to this one:

(SELECT * FROM memtable1) 
UNION ALL 
(SELECT * FROM table2)
LIMIT 1000 ;

The query asks for all the rows from memtable1, without any limit. Then the UNION is applied and the LIMIT 1000 is applied to the result of the UNION. It seems that the execution plan is not very efficient, and getting the rows of both tables in memory first (so 100 thousand rows) and then applying the limit.

If you want only 1000 rows from each table, try this:

(SELECT * FROM memtable1 LIMIT 1000) 
UNION ALL 
(SELECT * FROM table2 LIMIT 1000) ;

And if you want up to 1000 rows from both tables:

(SELECT * FROM memtable1 LIMIT 1000) 
UNION ALL 
(SELECT * FROM table2 LIMIT 1000) 
LIMIT 1000 ;
  • awesome explanation mainly for LIMIT. – Md Haidar Ali Khan Jul 26 '17 at 8:31
  • The first and final solutions presented would normally result in only the first 1000 rows from memtable being reported (unless it was < 1000 rows in size). – pojo-guy Jul 26 '17 at 12:15
  • @pojo-guy correct, under current implementation. There is no reason why the first 1000 rows of table2 wouldn't be shown instead though. – ypercubeᵀᴹ Jul 26 '17 at 12:18
  • 1
    If you also need OFFSET, it gets trickier, but still possible: LIMIT 4000 inside plus LIMIT 3000,1000 outside (for the 4th 'page'). – Rick James Jul 26 '17 at 13:59
1

Specifying the required column names instead of using '*' in select may reduce time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.