CODEWITHSUNDEEP
mysqlunion
user350230
user350230

Reputation: 73

Optimize a UNION mysql query

I'm using a UNION query to extract data from two tables based on date. Query below

SELECT title, id, date as date_added 
FROM test1 WHERE test1.id
UNION 
SELECT title, customer as id, date_added    
FROM test2 WHERE test2.id 
ORDER BY date_added DESC LIMIT 0,8

I have an index on date and date_added on each table...the problem is that the query isn't optimized and when I use EXPLAIN it shows all the rows of both the tables are being selected to give me the output.

Is there any other way of doing this query so that it can be optimized? The only solution i can think of is running the two queries separately with LIMITS and sorting the data in the application, but it seems it would be impossible to perform pagination in my application with that.

Upvotes: 1

Views: 2915

Answers (2)

Eddy
Eddy

Reputation: 5360

What you are basically showing is that you have a design issue in your model where it seems the wrong choice was made when implemententing super/subtypes. Your functional requirement is to have (simular) data from two different tables as one uniform set. This would be straightforward if all those rows had been in one table. So the real question is why they aren't.

You can still get this query faster (I assume) but it's ugly. 

SELECT * FROM 
   (SELECT * FROM (select title, id, date as date_added from test1 
                  ORDER BY date_added DESC LIMIT 0,8) t1
    UNION ALL 
    SELECT * FROM (select title, customer as id, date_added from test2 
                  ORDER BY date_added DESC LIMIT 0,8) t2
   ) joined
ORDER BY date_added DESC
LIMIT 0,8

Upvotes: 2

nikc.org
nikc.org

Reputation: 16952

I'm not the best expert, but I would assume that because of the UNION mysql needs to select and merge the two result sets before it performs the ORDERing, and thus, the potential result set optimization which might be provided by the LIMIT clause, doesn't happen.

Perhaps this question can apply to your case as well:

Combining UNION and LIMIT operations in MySQL query

Upvotes: 0

Related Questions