1

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.

user350230
  • 73
  • 1
  • 7
  • Your `WHERE` condition looks a bit truncated for the purpose of limiting the selected rows. Have you omitted something? – nikc.org Sep 03 '11 at 08:22
  • No, I haven't. Even if I remove the where clause the selected queries are still equal the total number of rows in each table. – user350230 Sep 03 '11 at 08:30
  • 1
    Well, they would, wouldn't they. You have no equality or other term to limit the `WHERE` statement, other than that `.id` needs to contain a value which evaluates to true. Normally it would have a limiting term such as `column = value` or `column > value` or `column between value1 and value2` etc. – nikc.org Sep 03 '11 at 08:32
  • Have you checked your indices? An index on `date_added` might help, although I doubt it will. – GolezTrol Sep 03 '11 at 08:37
  • Yes, I have and the indexes aren't helping the cause one bit. – user350230 Sep 03 '11 at 08:41
  • 1
    I think `nikc` was trying to say is your original query should contain `WHERE test1.id = /something/` (similarly for test2.) Otherwise, your `WHERE` servers no purpose and you're going to select every row. – searlea Sep 03 '11 at 10:30

2 Answers2

2

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
Eddy
  • 5,320
  • 24
  • 40
0

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

Community
  • 1
  • 1
nikc.org
  • 16,462
  • 6
  • 50
  • 83