I've got a query that seems to fall off a performance cliff. It does a fairly simple SELECT on a single table used for logging with a WHERE clause to limit the date range. If the query looks for a month of data, it takes about 7 minutes to run. If I ask for half-a-month, it takes 12 seconds. :-/ Yes, it's using the index on the datestamp field.

I started looking at server variables, wondering if the dataset is large enough that it crosses some sort of threshold that changes behaviour and thus the slowdown. I've doubled the sort_buffer, the read buffer, to no effect. I increased the maximum in-memory/temporary table size. Also no effect. Note I cannot restart the database: all changes must be done with SET GLOBAL var=value;

Anyone have any other ideas?

Wade.

The original query, actually generated by code I wrote being invoked by an end-user, was for a whole month. I fished the query out of the debugging.

I already knew a smaller time-frame affects this query. So I doubted it was data-based. But I did the sort of test you described: I ran it for the first half of the month: 7 seconds. Then I altered the dates for the second half of the month: 7 seconds. :-/ It's obviously the number of days. I can find the break point without too much more effort, but I don't think knowing that will win me anything.

Wade.

I had been using EXPLAIN. And USE INDEX. With it without USE INDEX, a month or half-a-month, it still uses the correct index. I'll try FORCE INDEX, though.

Wade.