MySQL slow group by/order by

Question

The following query is relatively slow (0.7 seconds with ~6k rows)

SELECT items.*, COUNT(transactions.ID)
   FROM items
  INNER JOIN users ON (items.USER = users.ID)
   LEFT JOIN transactions ON (items.id = transactions.item)
  WHERE items.ACTIVE = 1
    AND items.DELETED_AT IS NULL
  GROUP BY items.ID
  ORDER BY items.DATE DESC
  LIMIT 20

But speeds up dramatically when ordered by items.ID DESC instead of items.DATE. The transactions join is to a large table (~250k rows) and is one-to-many. The date column has an index.

Is there any way to generically improve the performance of the ORDER BY?

EDIT: indexes on items.user, transactions.item, and items.date. Items has 49 columns, users 76, and transactions 17.

Answer 1

Indexes can affect the performance of ORDER BY clauses. This MySQL manual page is probably worth your time. Essentially, if you order by a column that is part of the index that MySQL uses for the query, MySQL can use the index for the sort rather than the data itself.

In your particular query, the fact that the DATE column has an index doesn't matter, since that index probably isn't being used in your query. Your WHERE statement contains items.ACTIVE and items.DELETED_AT, and if those columns have an index that's being used for the WHERE that doesn't include the DATE column, then MySQL can't use the index to sort by DATE, and is likely resorting to a file sort.

If you can come up with an index that can be used by both the WHERE and the ORDER BY, you'd get an optimization boost. In this case, items.ACTIVE seems like a low cardinality column, so assuming items.DELETED_AT is a date, I would probably try an index like INDEX(DELETED_AT,DATE) for that table.

Use EXPLAIN SELECT... to see more about what's going on there too, you might get some further insights.