List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 25 1999 3:46pm
Subject:RE: Long Selects Different indexes used with same WHERE
View as plain text  
>>>>> "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
Thread
RE: Long Selects Different indexes used with same WHEREmikep24 Sep
  • RE: Long Selects Different indexes used with same WHEREMichael Widenius25 Sep