MySQL Forums
Forum List  »  Federated Storage Engine

Does union lose indexes?
Posted by: Ofer Kalisky
Date: June 02, 2009 05:51AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Does union lose indexes?
7363
June 02, 2009 05:51AM
4116
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.