slow response when where clause has a field from 2nd table in a left join

[Ravi Malghan]

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    |
+------+-------------+------------------+--------+---------------+-----------+---------+--------------------------------+----------+----------------+