Just a quick jot - I'm headed to the O'Reilly MySQL conference, April 12-14 in Santa Clara for three joyous days filled with database geekiness. There's a lot of diversity in my schedule for the conference - I'm focusing mainly on performance tuning for mySQL, cloud deployment, replication and sharding, and a shallow dip into noSQL strategies.
Here's the current problem I'm working on:
For any given table, (I don't believe that the ddl matters), say you have a timestamp field which is indexed and referenced in a query. The table is Innodb so the timestamp field can only be a btree index. Which means that the index is stored in ascending order.
In your query, your conditional reads something to the effect of (where timestamp(now) > timestamp(now) - 5 minutes) (it's psuedo-code, ok?)
What I expect to happen is that as the query is calculating the conditional border value and then doing an index-scan on the btree-index, examining each index value (row) until the current timestamp exceeds the calculated value.
What I'm seeing (in the slow-query log, among other places), is that the query is executing as an index-scan, but is scanning all the rows of the index, essentially doing a full table scan.
The query is calculated and stored as a variable passed to mysql_query() -- so the parser is determining the now()-5 minutes value and is passing it off as a string constant...
Anyone have any suggestions, I'd be most happy. This lil' kitteh turd has filled my slow-query litter box....