Query with HAVING clause is taking too long

Question

I have query like this:

SELECT  msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at',
        category, msg.`uid`,name, user_name, profile_pic, STATUS,
        isNotificationOn, onesignal_id, email, gender, phone,
        users.id userId, UNIX_TIMESTAMP(users.createdAt) 'createdAt',
        ( SELECT  COUNT(1)
            FROM  message_likes
            WHERE  uid = '006GA968vpb0O62xaOpKLuhR1cC3'
              AND  message_id = msg.id
        ) AS isLiked, 
        ( SELECT  COUNT(*)
            FROM  follower_list
            WHERE  follower_uid = '006GA968vpb0O62xaOpKLuhR1cC3'
              AND  following_uid = msg.uid) AS isFollowing,
        COUNT(DISTINCT share.uid) + msg.shares 'shares',
        COUNT(DISTINCT likes.uid) 'likes',
        msg.`application`,
        `isModified`, `modificationReason`, `isVoilation`, `voilationReason`,
        `followers_count`, `following_count`
    FROM  user_messages msg
    JOIN  users  ON msg.uid = users.uid
    LEFT JOIN  message_shares SHARE  ON msg.id = share.message_id
    left JOIN  message_likes likes   ON msg.id = likes.message_id
    LEFT JOIN  followers_count  ON msg.uid = followers_count.following_uid
    LEFT JOIN  following_count  ON msg.uid = following_count.follower_uid
    GROUP BY  msg.id
    HAVING  created_at > '0'
      AND  category LIKE '%%%'
      AND  msg.uid LIKE '%'
      AND  msg.application LIKE 'demo'
      AND  isLiked='1'
      AND  msg.id <= '999999'
      AND  isVoilation <= '1'
    ORDER BY  msg.id DESC
    LIMIT  10 OFFSET 0

This will take more than 8 seconds where my data is about 20k rows per table. But if I replace isLiked='1' with isLiked='%' it will execute within 1 second (60 - 70 ms) which is 1000 times faster. So what the mistake I am doing?

UPDATE: After moving conditions from having clause to where clause, i am getting responce in 2 seconds. Here is updated query:

SELECT  msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at',
        category, msg.`uid`,name, user_name, profile_pic, STATUS,
        isNotificationOn, onesignal_id, email, gender, phone,
        users.id userId, UNIX_TIMESTAMP(users.createdAt) 'createdAt',
        ( SELECT  COUNT(1)
            FROM  message_likes
            WHERE  uid = 'CqITnqfe5iZHT8x74zYxuEwzGMY2'
              AND  message_id = msg.id
        ) AS isLiked, 
        ( SELECT  COUNT(*)
            FROM  follower_list
            WHERE  follower_uid = 'CqITnqfe5iZHT8x74zYxuEwzGMY2'
              AND  following_uid = msg.uid) AS isFollowing,
        COUNT(DISTINCT share.uid) + msg.shares 'shares',
        COUNT(DISTINCT likes.uid) 'likes',
        msg.`application`,
        `isModified`, `modificationReason`, `isVoilation`, `voilationReason`,
        `followers_count`, `following_count`
    FROM  user_messages msg
    JOIN  users  ON msg.uid = users.uid
    LEFT JOIN  message_shares SHARE  ON msg.id = share.message_id
    left JOIN  message_likes likes   ON msg.id = likes.message_id 
    LEFT JOIN  followers_count  ON msg.uid = followers_count.following_uid
    LEFT JOIN  following_count  ON msg.uid = following_count.follower_uid
    WHERE category LIKE '%'
      AND  msg.uid like '%'
      AND  msg.application = 'shayari'
      AND  msg.id <= '999999'
      AND  isVoilation <= '1'
    GROUP BY  msg.id HAVING isLiked=1
     ORDER BY likes.created_at desc

Now the glitch is if i remove order by clause it will execute in few milliseconds and with order by clause it is taking about 2-3 seconds. Here is explain query:

Answer 1

There may be more suggestions, but these should help performance some...

        WHERE  uid = '006GA968vpb0O62xaOpKLuhR1cC3'
          AND  message_id = msg.id

Presumably, you have PRIMARY KEY(uid, message_id) ?

        WHERE  follower_uid = '006GA968vpb0O62xaOpKLuhR1cC3'
          AND  following_uid = msg.uid) AS isFollowing,

Some kind of index with both follower_uid and following_uid?

WHERE is done while gathering rows; HAVING is done after all the rows are gathered. That is, WHERE is more efficient. However, HAVING is required when working with aggregates; but you don't have such.

Don't include unnecessary things like foo LIKE '%'. Instead, build the query without it.

Discourage users from providing LIKEs with leading wildcards.

message_likes -- Don't COUNT something and then just check for 0 or 1. Instead, use EXISTS ( SELECT 1 FROM .. WHERE .. ) -- and this can be put directly in the WHERE (unless you also need to fetch the value).

"number = string" -- Avoid mixing numeric and non-numeric. In some situations, it requires all rows to be dynamically changed from a string to a number before checking.

("voilation" - check spelling)

How much data? What is the setting of innodb_buffer_pool_size? How much RAM?

created_at > '0' -- Huh? When you upgrade, beware of "zero dates". Consider changing to NULL instead of 0.