MYSQL slow down using UNION ALL 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?
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 ;