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.


ANSWERS:


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


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 


 MORE:


 ? MySql - Union across different Servers
 ? MySQL select inner join and union
 ? MySQL Union (or similar) query
 ? Mysql UNION with dynamic query
 ? MYSQL: UNION results between two tables where omitting records from first table if PK found in second table
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? How can I leverage an ORM for a database whose schema is unknown until runtime?
 ? Which ORM can preserve the correspondence between db schema and domain model?
 ? how to minimize application downtime when updating database and application ORM