Why isn't my index being used?

Maybe this is a naïve question but I'm trying to figure out why my
index is not being used during one query but it is on another with
slightly different numbers. Here are the table definitions in question
(with only the important information shown for brevity):

CREATE TABLE `parameter` (
`Id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`Id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `parameterlog` (
`ParameterId` int(10) unsigned NOT NULL default '0',
`SampleTimestamp` double NOT NULL default '0',
`Data` varchar(50) NOT NULL default '',
KEY `FK_parameterdata_Parameter` (`ParameterId`),
KEY `ParameterData_TimestampIndex` USING BTREE (`SampleTimestamp`),
CONSTRAINT `FK_parameterdata_Parameter` FOREIGN KEY (`ParameterId`)
REFERENCES `parameter` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


If I run this query:
select pl.*
from parameterlog pl
where
pl.ParameterId = 59 and
pl.SampleTimestamp >= 39505.2280861227 and
pl.SampleTimestamp <= 39505.9780861227;

It executes quickly (0.03 seconds) and explain outputs:
select_type = SIMPLE
table = pl
type = ref
possible_keys = FK_parameterdata_Parameter,
ParameterLog_TimeStampIndex
key = ParameterLog_TimeStampIndex
key_len = 8
ref = null
rows = 4332
extra = Using where

If I run this query:
select pl.*
from parameterlog pl
where
pl.ParameterId = 59 and
pl.SampleTimestamp >= 39504.2280861227 and
pl.SampleTimestamp <= 39504.9780861227;

It executes VERY slowly (6.6 seconds) and explain outputs:
select_type = SIMPLE
table = pl
type = ref
possible_keys = FK_parameterdata_Parameter,
ParameterLog_TimeStampIndex
key = FK_parameterdata_Parameter
key_len = 4
ref = const
rows = 28832
extra = Using where

So in the first case it decided to use the index on the timestamp but
in the second case it decided to use the FK on the parameter index. I
don't quite get how it's making this decision or what I need to do
always use the timestamp index in this query.

I should also note that if I remove the FK from parameterlog to
parameter then it uses the other index both times.

On Mon, 03 Mar 2008 18:16:05 +0100, Israel <israeldiperi@hotmail.com>
wrote:
> Maybe this is a naïve question but I'm trying to figure out why my
> index is not being used during one query but it is on another with
> slightly different numbers. Here are the table definitions in question
> (with only the important information shown for brevity):
>
> CREATE TABLE `parameter` (
> `Id` int(10) unsigned NOT NULL auto_increment,
> PRIMARY KEY (`Id`),
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> CREATE TABLE `parameterlog` (
> `ParameterId` int(10) unsigned NOT NULL default '0',
> `SampleTimestamp` double NOT NULL default '0',
> `Data` varchar(50) NOT NULL default '',
> KEY `FK_parameterdata_Parameter` (`ParameterId`),
> KEY `ParameterData_TimestampIndex` USING BTREE (`SampleTimestamp`),
> CONSTRAINT `FK_parameterdata_Parameter` FOREIGN KEY (`ParameterId`)
> REFERENCES `parameter` (`Id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
>
> If I run this query:
> select pl.*
> from parameterlog pl
> where
> pl.ParameterId = 59 and
> pl.SampleTimestamp >= 39505.2280861227 and
> pl.SampleTimestamp <= 39505.9780861227;
>
> It executes quickly (0.03 seconds) and explain outputs:
> select_type = SIMPLE
> table = pl
> type = ref
> possible_keys = FK_parameterdata_Parameter,
> ParameterLog_TimeStampIndex
> key = ParameterLog_TimeStampIndex
> key_len = 8
> ref = null
> rows = 4332
> extra = Using where
>
> If I run this query:
> select pl.*
> from parameterlog pl
> where
> pl.ParameterId = 59 and
> pl.SampleTimestamp >= 39504.2280861227 and
> pl.SampleTimestamp <= 39504.9780861227;
>
> It executes VERY slowly (6.6 seconds) and explain outputs:
> select_type = SIMPLE
> table = pl
> type = ref
> possible_keys = FK_parameterdata_Parameter,
> ParameterLog_TimeStampIndex
> key = FK_parameterdata_Parameter
> key_len = 4
> ref = const
> rows = 28832
> extra = Using where
>
> So in the first case it decided to use the index on the timestamp but
> in the second case it decided to use the FK on the parameter index. I
> don't quite get how it's making this decision or what I need to do
> always use the timestamp index in this query.

As to the why, I can't help you, however, you can tell MySQL which
index/key to use:
<http://dev.mysql.com/doc/refman/5.1/en/index-hints.html>
--
Rik Wasmus