Is this an inefficient request?

Question

Assuming table1 and table2 have a large number of rows (i.e. several hundred thousand), is the following inefficient query?

Edit: sort by field.

SELECT * FROM ( SELECT title, updated FROM table1 UNION SELECT title, updated FROM table2 ) AS query ORDER BY updated DESC LIMIT 25 

Answer 1

If you absolutely need different results, another possibility is to use union all and group by :

 SELECT title FROM ( SELECT title FROM table1 group by title UNION ALL SELECT title FROM table2 group by title ) AS query group by title LIMIT 25; 

Testing this without the limit clause in a column with an indexed identifier from two tables with ~ 920K rows in the test database (in $work ) resulted in a bit per second with a query higher and about 17 seconds through a union .

Answer 2

it should be even faster - but then I don't see ORDER BY, so what 25 records do you really want?

 SELECT * FROM ( SELECT title FROM table1 LIMIT 25 UNION SELECT title FROM table2 LIMIT 25 ) AS query LIMIT 25 

Answer 3

UNION must make an extra pass to extract distinct records, so you must use UNION ALL .

Answer 4

Yes, use the order and restrictions in internal queries.

 SELECT * FROM ( (SELECT title FROM table1 ORDER BY title ASC LIMIT C) UNION (SELECT title FROM table2 ORDER BY title ASC LIMIT C) ) AS query LIMIT 25 

This will only go through lines C instead of N (hundreds of thousands). ORDER BY is required and must be in an indexed column.

C is a heuristic constant that must be configured according to the domain. If you expect only a few duplicates, C = 50-100 is probably normal.

You can also find out for yourself using EXPLAIN.