MySQL Forums
Forum List  »  Performance

slow response when where clause has a field from 2nd table in a left join
Posted by: Ravi Malghan
Date: February 12, 2016 12:51PM

I have a view that is using left join to join two tables. When my where clause uses a field from the 2nd table, the query runs really slow. I need to use a left join since I want rows from table 1 listed even if they don't have a corresponding row in 2nd table.

Table 1
CREATE TABLE `all_alarms` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `alarm_key` varchar(128) NOT NULL,
  `SERVERSERIAL` int(16) NOT NULL,
  `SERVERNAME` varchar(64) NOT NULL,
  `FIRSTOCCURRENCE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `OBJECT_ID` varchar(64) DEFAULT NULL,
.
.
   PRIMARY KEY (`id`,`FIRSTOCCURRENCE`),
  KEY `OBJECT_ID` (`OBJECT_ID`),
  KEY `alarm_key` (`alarm_key`));

Table 2
CREATE TABLE `current_alarms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket` varchar(255) DEFAULT NULL,
  `element` varchar(255) DEFAULT NULL,
  `alarm_key` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `alarm_key` (`alarm_key`),
  KEY `element` (`element`)
)

View
CREATE VIEW history AS select 
`all_alarms`.`alarm_key` AS `alarm_key`
`all_alarms`.`SERVERSERIAL` AS `SERVERSERIAL`,
`all_alarms`.`SERVERNAME` AS `SERVERNAME`,
.
`all_alarms`.`OBJECT_ID` AS `OBJECT_ID`,
`current_alarms`.`element` AS `element`
from (`all_alarms` inner join `current_alarms` on((`all_alarms`.`alarm_key` = `current_alarms`.`alarm_key`)));

This query runs fine (5-10 seconds)
select * from history where `OBJECT_ID`='abc'
But this one takes a long time (upto 40 seconds)
select * from history where `OBJECT_ID`='abc' or `element`='abc'

Following is what my explain plan shows. DOn't see it using the case_name index from the 2nd tables
explain select * from `history` where `cascade` = 'abc' or `element` = 'abc';
+------+-------------+------------------+--------+---------------+-----------+---------+--------------------------------+----------+----------------+
| id   | select_type | table            | type   | possible_keys | key       | key_len | ref                            | rows     | Extra          |
+------+-------------+------------------+--------+---------------+-----------+---------+--------------------------------+----------+----------------+
|    1 | SIMPLE      | all_alarms   | ALL    | OBJECT_ID       | NULL      | NULL    | NULL                           | 10705411 | Using filesort |
|    1 | SIMPLE      | current_alarms | eq_ref | alarm_key     | alarm_key | 130     | all_alarms.alarm_key |        1 | Using where    |
+------+-------------+------------------+--------+---------------+-----------+---------+--------------------------------+----------+----------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
slow response when where clause has a field from 2nd table in a left join
1178
February 12, 2016 12:51PM


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.