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]
