CODEWITHSUNDEEP
mysqlperformancejoinsql-order-by
Seliquity
Seliquity

Reputation: 61

MySQL slow group by/order by

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.

Upvotes: 6

Views: 5661

Answers (3)

Sanni Poriya
Sanni Poriya

Reputation: 161

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

The above query works perfect , i have used it in a very long database . It ORDER BY the listing of 10 OR (xx) items which we are getting from inner select query, so it's very fast!

Upvotes: 2

xQbert
xQbert

Reputation: 35323

These are things that COULD (read not guaranteed) help.

  1. Eliminate the * on items.* and list each field individually. 49 columns is a lot; do you really need them all?
  2. Usually the engine optimizes the queries such that limiting criteria is considered on joins. Perhaps the plan used by the engine is not doing this (need explain plan results to know) so re-arranging the where clause and joins MAY (not likely) help by forcing the engine to consider this. (See below)
  3. Rebuild table statistics if many update, insert, deletes have occurred over time, it's possible the table statistics are off and need to 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

Upvotes: 1

zombat
zombat

Reputation: 94157

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.

Upvotes: 5

Related Questions