Question 112796 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    This will significantly speed up the subquery:

    INDEX(phone)

    I assume you already have `PRIMARY KEY(id).

    This reformulation may improve the query plan:

    SELECT messages.*
    FROM ( SELECT MAX(id) AS maxid FROM messages GROUP BY phone ) AS x
    JOIN messages  ON messages.id = x.maxid
    WHERE  chat = :ch
      AND  status = :st
      AND  seller_id = :seller
    ORDER BY  created_at DESC

    A stored procedure will not help enough to warrant its usage.

    EXPLAIN SELECT ... and SHOW CREATE TABLE should always be included when asking performance questions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search