Indexes And Sorting

I'm running the following query on a table with an index on dateLast,
and one on status:

SELECT field FROM table
WHERE (DATEDIFF(NOW(), dateLast) > 7) AND (status = 1)
ORDER BY dateLast ASC
LIMIT 10

This query takes about 80 seconds, because MySQL uses the index on
'status', which means it has to sort the rows using filesort.

When I remove 'status' from the query:

SELECT field FROM table
WHERE (DATEDIFF(NOW(), dateLast) > 7)
ORDER BY dateLast ASC
LIMIT 10

The query doesn't even take a second, because MySQL can use the index
on dateLast to both check the where condition and sort the rows.

Any ideas? Is there a way to tell MySQL to order rows using a certain
index?

On 10.05.2007 13:59, nk wrote:
> I'm running the following query on a table with an index on dateLast,
> and one on status:
>
> SELECT field FROM table
> WHERE (DATEDIFF(NOW(), dateLast) > 7) AND (status = 1)
> ORDER BY dateLast ASC
> LIMIT 10
>
> This query takes about 80 seconds, because MySQL uses the index on
> 'status', which means it has to sort the rows using filesort.
>
> When I remove 'status' from the query:
>
> SELECT field FROM table
> WHERE (DATEDIFF(NOW(), dateLast) > 7)
> ORDER BY dateLast ASC
> LIMIT 10
>
> The query doesn't even take a second, because MySQL can use the index
> on dateLast to both check the where condition and sort the rows.
>
> Any ideas? Is there a way to tell MySQL to order rows using a certain
> index?

Did you try a covering index on (dateLast, status) or (status, dateLast)?

robert

nk schrieb:
> Any ideas? Is there a way to tell MySQL to order rows using a certain
> index?

Yes, but that should be the last resort.
It may be using the wrong index because its idea about the selectivity
of each index is wrong; this can be rectified via ANALYZE TABLE.

Other than that, I'd use Robert Klemme's advice and use an index on
(status, dateLast).

You may also try computing the cut-off date first, then write something like
SELECT field FROM table
WHERE dateLast < [cutoff_date] AND (status = 1)
ORDER BY dateLast ASC
LIMIT 10

I'm not sure that the optimizer knows that DATEDIFF is a monotonic
function. If it doesn't, it cannot really use the dateLast field just
because it is used in DATEDIFF(NOW(), dateLast) > 7.

In general, non-monotonous expressions make using an index almost
useless. Consider, for example,
SELECT foo FROM t WHERE SIN(foo) BETWEEN 0.1 AND 0.2
Using an index on foo is almost useless in this case. It's possible that
mysql cannot tell the difference between SIN(_) and DATEDIFF(NOW(),_),
and even adding another index might not change that.
The cutoff_date trick will always work, of course :-)

Regards,
Jo

On Thu, 10 May 2007 14:06:13 +0200, Robert Klemme
<shortcutter@googlemail.com> wrote:

>Did you try a covering index on (dateLast, status) or (status, dateLast)?

From what i understood, (datelast,status) would be the best choice :
- (datelast, status) lets you use the datelast index by itself,
- (status,datelast) allows only using status and (status,datelast).

So in that case where datelast seems to be important, better to have
it first.

Using the (datelast, status) index would probably work, but
unfortunately I also have an index on ('status','somethingelse') in
the table, which I need for another query. Since this index's
cardinality is lower than the one of (datelast, status), MySQL chooses
the wrong index and I still have the same problem.

Any idea how I can force MySQL to use a certain index?

On Tue, 05 Jun 2007 07:06:53 -0700, nk <nkoelling@gmail.com> wrote:

>Using the (datelast, status) index would probably work, but
>unfortunately I also have an index on ('status','somethingelse') in
>the table, which I need for another query. Since this index's
>cardinality is lower than the one of (datelast, status), MySQL chooses
>the wrong index and I still have the same problem.
>
>Any idea how I can force MySQL to use a certain index?

SELECT blabla FROM table USE INDEX myindex WHERE ...
or if it's really not willing to use it :
SELECT blabla FROM table FORCE INDEX myindex WHERE ...