>>>>> "mikep" == mikep <mikep@stripped> writes:
mikep> Why don't the two statements use the same indexes?
mikep> SELECT HIGH_PRIORITY count(*)
mikep> FROM gas_alarm_attributes, gas_alarms
mikep> WHERE gas_alarms.internal_id = gas_alarm_attributes.alarm_id AND state=
mikep> 'open' AND timestamp <= FROM_UNIXTIME(938200363);
<cut>
mikep> SELECT HIGH_PRIORITY gas_alarms.internal_id, alarm_level, about_node,
mikep> domain, about_process, message_text, state, alarm_type,
mikep> DATE_FORMAT(timestamp, "%m/%d %H:%i:%s"), gas_alarm_attributes.internal_id
mikep> FROM gas_alarm_attributes, gas_alarms
mikep> WHERE gas_alarms.internal_id = gas_alarm_attributes.alarm_id AND state =
mikep> 'open' AND timestamp <= FROM_UNIXTIME(938200363) LIMIT 0, 25;
Hi!
If you are using LIMIT MySQL tries to take into account that you don't have to
solve the whole result set to find the matching rows and this can
cause MySQL to use different keys. Without a lot more information
(like key distribution in the tables and how many rows are involved in
the result) I can't say exactly why this happens in this case. Note
that you can force a specific table order with STRAIGHT_JOIN.
mikep> Also, at one time I had an index on gas_alarms using the internal_id and
mikep> state and another index on gas_alarm_attributes using alarm_id and
mikep> timestamp. The explain statement said the two indexes were being used but
mikep> both the SELECT count(*) and the regular select were painfully slow, between
mikep> 45 sec and 70 sec each. I remove the index on gas_alarm_attributes and the
mikep> selects started coming back much more quickly. Does this make sense?
In normal cases its faster with more indexes, but in some case it may
be faster to scan the rows instead of using indexes (in particular if
you have a lot of identical keys). Sorry but the same thing applies
here; Without access to the tables + some examples, its pretty hard
to say what when wrong.
Regards,
Monty
