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, you only want 5 records in your results (the 5 with the lowest ID values). It doesn't matter if those 5 come from the first inner query or the second but you will use at most 5 records from either. By reducing the number of records you need to UNION, you seriously decrease the processing required to achieve your outer goal. Granted, this is a rather special case but this is the second time this week that this pattern of query has appeared on the list so it can't be too special. To get more abstract in analyzing an index merge process, can we discuss under which conditions it would be useful to merge indexes? How would you describe the initial conditions of any UNION query that would describe a situation in which index merging would be of benefit to the execution of the query. I think it would be useful in more situations than when UNIONing the complete table query against two identical tables, if we can describe the decision in more general terms. Could you use index merging during JOINs or just UNIONs? What other types of queries could benefit from this being added to the query engine? Is it only useful in LIMIT queries? Come on everyone, put on your thinking caps and work like DB developers for a bit and let's see if we can' t hash out a set of parameters that make sense. This sounds like a useful tool to add to the core engine but I think it needs a bit of thought about when to use it and when not to use it. What are your ideas? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eli Hen <[EMAIL PROTECTED]> wrote on 08/03/2005 04:44:55 PM: > 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 got 2 tables which are defined the same way but different names. > > mysql> SHOW CREATE TABLE t1 \G > *************************** 1. row *************************** > Table: t1 > Create Table: CREATE TABLE `t1` ( > `id` int(11) NOT NULL auto_increment, > `a` char(10) NOT NULL, > PRIMARY KEY (`id`), > KEY `a` (`a`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > 1 row in set (0.00 sec) > > mysql> SHOW CREATE TABLE t2 \G > *************************** 1. row *************************** > Table: t2 > Create Table: CREATE TABLE `t2` ( > `id` int(11) NOT NULL, > `a` char(10) NOT NULL, > PRIMARY KEY (`id`), > KEY `a` (`a`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > 1 row in set (0.00 sec) > > > mysql> EXPLAIN (SELECT * FROM t1 WHERE a='a') UNION (SELECT * FROM t2 > WHERE a='a') ORDER BY id LIMIT 0,5 \G > *************************** 1. row *************************** > id: 1 > select_type: PRIMARY > table: t1 > type: ref > possible_keys: a > key: a > key_len: 10 > ref: const > rows: 1000 > Extra: Using where > *************************** 2. row *************************** > id: 2 > select_type: UNION > table: t2 > type: ALL > possible_keys: a > key: NULL > key_len: NULL > ref: NULL > rows: 500 > Extra: Using where > *************************** 3. row *************************** > id: NULL > select_type: UNION RESULT > table: <union1,2> > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: NULL > Extra: Using filesort > 3 rows in set (0.01 sec) > > MySQL executes each sub-query seperatedly (which returns 1000 and 500 > rows), then it combines them and use filesort to order, then it limits > and give only the first 5 rows. > This is very expensive for just 5 first rows that match the same WHERE > clause from 2 tables which have the SAME definition and indexes. It > might be possible to merge the indexes of t1 and t2 and speed up the > query... Is it possible? Does MySQL intend to do this? > > [BTW: I gave just a private case. It might be examined to cover more cases.] > > -thanks in advance > > > >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: ref > >possible_keys: a > > key: a > > key_len: 5 > > ref: const > > rows: 1 > > Extra: Using where; Using index > >*************************** 2. row *************************** > > id: 2 > > select_type: UNION > > table: t1 > > type: ref > >possible_keys: a > > key: a > > key_len: 5 > > ref: const > > rows: 1 > > Extra: Using where; Using index > >*************************** 3. row *************************** > > id: NULL > > select_type: UNION RESULT > > table: <union1,2> > > type: ALL > >possible_keys: NULL > > key: NULL > > key_len: NULL > > ref: NULL > > rows: NULL > > Extra: > >3 rows in set (0.00 sec) > > > > > >mysql> show create table t1\G; > >*************************** 1. row *************************** > > Table: t1 > >Create Table: CREATE TABLE `t1` ( > > `a` int(11) default NULL, > > KEY `a` (`a`) > >) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > > > > > > >> 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.. > >> > >> -thanks in advance. > >> > >> > >> > > > > > >-- > >For technical support contracts, goto https://order.mysql.com/?ref=ensita > >This email is sponsored by Ensita.NET http://www.ensita.net/ > > __ ___ ___ ____ __ > > / |/ /_ __/ __/ __ \/ / Gleb Paharenko > > / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@/stripped/ > >/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > > <___/ www.mysql.com <http://www.mysql.com> > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
