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….