Re: trouble optimizing query - taking much longer than expected

[Rick James]

I'm confused...

> By default, however, it is capture_timestamp DESC and all dates - just as the original query I posted. I am more concerned with this than anything as it is the display which shows to the user... by default... before they want to customize the sorting or date range.

That says to me that you have a canned query that you present _before_ they get to choose various options (date range, etc). But

> Again, this is the default value so...

This implies that no SQL is run until they have chosen which defaults to override.

If it is the first case, then we need to focus on optimizing the one default SQL statement; that was the direction I was aiming in my previous response.

If it is the second case, then I suggest the defaults should be more restrictive. And design it so that the restrictive defaults would lead to a faster query. The user would have to do work to get a slower query.

For example, I had a dataset with several years' worth of data, but usually the user was most interested in the last week's data, sometimes that last month's worth. So I optimized the schema (by using PARTITIONing and other techniques) to make accessing the last month's data very fast, letting 6-month-old data be slower. The UI asked for something like "How many days to go back" and the fill in was defaulted to 7.