Search code examples
mysqlsqlmariadbquery-optimization

MAX() slow query

I have this query to fetch most recent conversations grouped by phone number, however the query time is too slow. How can it be faster?

SELECT * from messages WHERE id IN (SELECT max(id) from messages GROUP BY phone) AND chat = :ch AND status = :st AND seller_id = :seller ORDER BY created_at DESC

DB Version 10.2.44-MariaDB

Solution

  • One of the main ways to speed up a query is to add an index to the column(s) involved. Adding an index to the id column will speed up the query (if there isn't an index on it already).

    As @danblack mentions in the comments, MariaDB has an EXPLAIN feature that will analyze a query. The analysis can then be used to optimize the query.

    There also are more sophisticated techniques for speeding up a query, such as writing a stored procedure, if the above doesn't work