Mysql query optimise-CodePudding

Mysql query takes 15 sec to fetch data. I have given my query below.

FYI: messages table has 5L (500,000) rows of data so it takes long time.

select  distinct
        ur.id, ur.user_name as name, ur.online, ur.chat_status,
        ur.updated_at, ur.profile_image, ur.role_id, 
        ( SELECT  created_at
            from  messages
            where  from_role_user_id = ur.id
              OR  to_role_user_id = ur.id
            Order by  created_at DESC
            LIMIT  1
        ) as message_at, 
        ( SELECT  count(is_read)
            from  messages
            where  from_role_user_id = ur.id
              AND  to_role_user_id = 1
              AND  is_read = 0
        ) as count,
        r.name as role
    from  role_users ur
    left join  roles r  ON r.id = ur.role_id
    where  ur.id != 1
      AND  r.name IN ('superadmin', 'candidate', 'admin', 'manager',
                      'business_unit','client')
      AND  ur.chat_status != 'offline'
      AND  ur.is_deleted = 0
      AND  ur.user_name IS NOT NULL
    order by  message_at DESC
    LIMIT  10;

Can any one help me to optmize the query.

CodePudding user response：

The first subquery would probably be faster this way:

  ( SELECT  MAX(created_at)
      from  messages
      where  from_role_user_id = ur.id
         OR  to_role_user_id = ur.id
  ) as message_at,

This may be even faster:

  SELECT GREATEST(
             ( SELECT MAX(created_at)  FROM messages
                 WHERE from_role_user_id = ur.id ),
             ( SELECT MAX(created_at)  FROM messages
                 WHERE to_role_user_id = ur.id ) )

with two indexes:

INDEX(from_role_user_id, created_at), INDEX(to_role_user_id, created_at)

COUNT(x) counts how many rows have x IS NOT NULL. SUM(y) adds up the y's, ignoring NULLs. The usual way to count rows is COUNT(*).

  ( SELECT  count(*)
      from  messages
      where  from_role_user_id = ur.id
        AND  to_role_user_id = 1
        AND  is_read = 0
  ) as count,

And have `INDEX(from_role_user_id, to_role_user_id, is_read)
If the DISTINCT is not really needed, it will avoid an extra pass over the data, hence faster.
Please provide EXPLAIN SELECT ...
It may be better to find the ids of the 10 messages first, then go to the effort of running those subqueries on ly 10 times.