Hi guys,

I have a table with over a million rows in it. Lately I've noticed some slowness even on some simple queries. Maybe I'm not understanding something obvious about indexes so I'm hoping some one can enlighten me.

Each record has a datetime field to keep up with when that record was created. If I run the following query:

select * from table where datetime > '2012-01-01'

It won't use the index that I have placed on the datetime field.

If I run this next query, however:

select 1 from table where datetime > '2012-01-01'

It uses the index fine. Does the index only apply to itself? If I want to reference multiple columns, do I need to create an index on multiple fields?

This is where things get weird. If I run this query:

select * from table where datetime > '2012-06-01'

It uses the index fine. Is whether or not it uses the index some how dependent on the number of rows returned? Why does the index work when selecting just the last couple of months, but not since the beginning of the year?

Any help would be appreciated.

Another bit of useful information is that the table's storage engine is InnoDB.

How do you know when the index is used and when it is not used?

Explain or describe tells you how the SQL engine processes the query
MySQL :: MySQL 5.0 Reference Manual :: 8.2.1 Optimizing Queries with EXPLAIN

This is exactly right.

Im wondering if you should be expressly converting the date literal to a datetime

it could be a quirk of the data that is in the db. ie the parser may decide its quicker to do a tablescan than use an index if the where clause is going to return most if not all rows

another thought is that perhaps the indexes need rebuilding

I wondered the same thing about converting and I've tried it and it doesn't make a difference. Furthermore MySQL documentation very plainly states that the way to do datetime comparisons to a static date is to compare to a string in the format of 'YYYY-mm-dd hh:mm:ss'.

Where clause returns as little as 20% of the rows when the index isn't being utilized.

I might try rebuilding indexes.. on a million plus row table though that is no small feat...

I ran an optimize table query on it, which with innodb tables essentially rebuilds the table. It took about 20 minutes. Looks to be working much better now.

Yup. I probably misunderstood the original post My thinking was that it would be tough (impossible?) to determine "after the run".

As the query and the data content and possibly the db definition might have changed along the way, the EXPLAIN might show unrelated results.

Good to hear the time problem has been improved.