You're right.. Your suggestion will speed it up.. but if you want to
have LIMIT 1000,10 then you will have to retrieve 1010 rows from each
sub-query then order the UNIONed rows and get the 10 rows you want. Here
is just 2 sub-queries, but what if you got 10 UNIONed sub-queries.. and
what if you
I am not sure about index merging but you should be able to speed things
up if you ORDER BY and LIMIT your inner queries as well:
(SELECT * FROM t1 WHERE a='a' ORDER BY id limit 0,5)
UNION
(SELECT * FROM t2 WHERE a='a' ORDER BY id limit 0,5)
ORDER BY id LIMIT 0,5
To answer your UNION query, y
Hi,
In the example you gave, it seems that MySQL doesn't merge the index of
t1 in both sub-queries (which is the same index).. but it runs the
sub-queries seperatedly, using the index on each sub-query seperatedly..
Mabye I wasn't clear enough with my question.. let me phrase it again:
Say I
Hello.
MySQL uses indexes in queries which are parts of UNION. See:
mysql> explain (select * from t1 where a=5) union (select * from t1 where
a=3)\G;
*** 1. row ***
id: 1
select_type: PRIMARY
table: t1
type:
Hello,
MySQL implemented index_merge in version 5...
Does MySQL supports something like index_merge to speed up UNION
queries? If yes, for which version (assumed release time)?
Does anyone know of other DBs systems that can speed up UNION queries?
This issue is quite critical for our system..
