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
SELECT * FROM table2 LIMIT 1000
SELECT * FROM memtable1 UNION ALL SELECT * FROM table2 LIMIT 1000
(1 min 21 sec.)
How can I improve the speed of the query?