Here is my table structure:
station : varchar(10)
ob : datetime
obtype : char(1)
dew : double
primary key is: station, ob, obtype
dew is an index
ob is an index
using "explain," the following query looks good:
EXPLAIN SELECT ob, temp
FROM cronos.hourly
WHERE station = 'LAKE'
AND ob >= '2006-08-01'
AND ob <= '2006-12-14'
AND obtype = 'H'
ORDER BY ob ASC
only a few dozen-thousand rows. But when I do this query, the number of rows increase to over 2 million:
EXPLAIN SELECT ob, temp
FROM cronos.hourly
WHERE station = 'LAKE'
AND ob >= '2006-08-01'
AND ob <= '2006-12-14'
AND obtype = 'H'
AND dew < - 2.2
ORDER BY ob ASC
The only difference is "dew < -2.2" For every obtype='H' there are also 60 obtype='O' values. I don't care about those. I only want the second query to return the rows where obtype='H' and the dew is < -2.2. It does that, but it takes forever because it's looking through over 2 million rows. It does not appear to be using my index the way I expected. Shouldn't it be?
Why aren't the number of rows given by explain the same as the first query? Is there a way I can optimize this? I want the "dew < -2.2" clause to look only at the stuff from the primary key.
Thanks,
Mark
