Does union lose indexes?
I have a table:
CREATE TABLE `session_1` (
`Id` bigint(20) NOT NULL auto_increment,
`SessionId` bigint(20) NOT NULL,
`StartTime` datetime default NULL,
`EndTime` datetime default NULL,
PRIMARY KEY (`Id`),
KEY `SessionId` (`SessionId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and another table:
CREATE TABLE `session_2` (
`Id` bigint(20) NOT NULL auto_increment,
`SessionId` bigint(20) NOT NULL,
`StartTime` datetime default NULL,
`EndTime` datetime default NULL,
PRIMARY KEY (`Id`),
KEY `SessionId` (`SessionId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
then I want to union them:
create view sessions as (select *,"1" as server_id from session_1) union all (select *,"2" as server_id from session_2);
now, running this:
SELECT * FROM session_1 where SessionId=123;
is fast and:
SELECT * FROM session_2 where SessionId=123;
but running:
SELECT * FROM session where SessionId=123;
is slow.
here's their explanation:
EXPLAIN SELECT * FROM session_1 where SessionId=123;
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | session | ref | ContractId | ContractId | 8 | const | 1 | |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------+
EXPLAIN SELECT * FROM session where SessionId=123;
+----+--------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 144792 | Using where |
| 2 | DERIVED | session | ALL | NULL | NULL | NULL | NULL | 75735 | |
| 3 | UNION | session | ALL | NULL | NULL | NULL | NULL | 75735 | |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+------+---------------+------+---------+------+--------+-------------+
can it be that the union lost my indexes?
Subject
Views
Written By
Posted
Does union lose indexes?
7472
June 02, 2009 05:51AM
4168
June 16, 2009 08:13AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.