mysqlselectsql-order-by

MySQL slow query featuring ORDER BY

I have the following SELECT that runs in under a second if I remove the ORDER BY at the end. With the ORDER BY it runs in around 9 seconds.

I have a rough understanding that some other SQL commands like '< >' comparison operators can work adversely with an ORDER BY.

I've tried commenting out lines and can improve the performance to say 2-3 seconds with the ORDER BY but still without the ORDER BY it's more like under 1 second.

How can I better write this query? What constructs should I avoid?

Solution

  • please try:

    SELECT * FROM (
  SELECT .. // Your old Query without GROUP BY and LIMIT
  ...  
) AS tmp
ORDER BY heartbeat DESC
LIMIT 120;