Mysql query gets extremely slow when using union, is there any other way to avoid union, or increase the efficiency of the query

Question

We've got 2 tables in our project, one for warehouse stock in, and one for out. But now we need to put them together in one query, which of course comes with query conditions like 'time','warehouse_id',etc., the result needs to be sorted as well. The solution was to union these 2 tables, and then sort, the query looks like:

select * from
(select ... from warehouse_in left join ... where ... 
union
select ... from warehouse_out left join ... where ...) as tmp order by 'stock_time' limit X,Y

But the problem is that this query runs extremely slow, it takes 1.5s to execute on less than 100k records, which usually take less than 0.05s if we don't use union. Is there any other way to improve the query speed?

Answer 1

Thank you all for answering, a single big table could work, i'll try this. Besides, I found another solution which is to sort and limit in each half of the query with a parameter of last_record_time, and then union the result and do sort and limit again. I prefer to build a single table.

Answer 2

Change UNION to UNION ALL unless you are expecting duplicate rows. UNION does a de-dup pass over the data.

Add ORDER BY and LIMIT Z to the inner queries. Z=X+Y. More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or