Mysql slow group in order

Question

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

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 it accelerates dramatically when ordering items.ID DESC instead of items.DATE. Mergers of transactions consist in a large table (~ 250 thousand lines) and is one-to-many. The date column has an index.

Is it possible to improve the performance of ORDER BY at all?

EDIT: indexes for items.user, transaction.item and items.date. Elements have 49 columns, users 76 and transactions 17.

Answer 1

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

In your particular query, the fact that the DATE column has an index does not matter, since that index is probably not used in your query. Your WHERE statement contains items.ACTIVE and items.DELETED_AT , and if these columns have an index that is used for WHERE that does not include a DATE column, then MySQL cannot use the index to sort by DATE and probably resort to sorting the file.

If you can find an index that can be used by both WHERE and ORDER BY , you will get an optimization boost. In this case, items.ACTIVE looks like a low power column, so if items.DELETED_AT is a date, I would probably try an index like INDEX(DELETED_AT,DATE) for this table.

Use EXPLAIN SELECT... to find out more about what's going on there, you can get more information.

Answer 2

SELECT * FROM (SELECT * FROM wp_users WHERE 1 GROUP BY ID limit 0.10) as X ORDER BY ID DESC

The above query works fine, I used it in a very long database. It ORDERS a listing of 10 OR (xx) elements that we get from an internal selection request, so its very FAST !!

Answer 3

These are things to PRAY (reading is not guaranteed).

• Eliminate * on the elements * and list each field separately. 49 columns, do you really need them all?
• Typically, the engine optimizes the queries, so that the restriction criteria are taken into account when combining. Perhaps the plan used by the engine does not do this (you need to explain the results of the plan in order to know), so reordering the where clause and attaching MAY (hardly) to help forcing the engine to take this into account. (See below).
• Rebuild table statistics, if many updates, inserts, deletes have over time, perhaps the table statistics are turned off and should be rebuilt for each table

.

 SELECT items.[list fields], COUNT(transactions.ID) FROM items INNER JOIN users ON (items.USER = users.ID) AND items.Active=1 AND items.DELETED_AT is Null LEFT JOIN transactions ON (items.id = transactions.item) GROUP BY items.ID ORDER BY items.DATE DESC LIMIT 20