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

[Øystein Grøvlen]

Hi,

Your post leaves me a bit confused. You say that you need to use LEFT JOIN, but your view uses INNER JOIN. You talk about a "case_name index" index, but I do not see any index nor column with that name in the table definitions.

Anyhow, I am not surprised that your query takes much longer when adding an OR condition. Without the second condition, only rows from the first table with "cascades = 'abc'" need to be considered. With the OR condition, any row of the first table might be a candidate. Hence, a lot more rows will have to be looked up in the second table. I would think you can speed up the query by adding a combined index on current_alarms(alarm_key, element), but it will never be as fast as the query without the second condition.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway