0

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:

EXPLAIN SELECT

  • Do you have an index on isLiked? If it's set to %, then the optimiser may realise that it doesn't have to bother to do any searching/scanning and just returns a result, but it you have a value specified ('1'), then the optimiser has to perform a search/scan! – Vérace Feb 16 '19 at 15:17
  • 2
    We need to see SHOW CREATE TABLE and EXPLAIN SELECT ... – Rick James Feb 16 '19 at 17:15
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.

| improve this answer | |
  • Thank you so much. By replacing having with where and using EXIST () solved my problem, Now it's taking less than 1 second. Regarding "foo LIKE '%' and created_at > '0'" those data will come dynamically from the server according to request so I placed conditions. Data is something 50k+ rows in every table which increases daily. RAM is 8gb of server and innodb_buffer_pool_size is 1G – shivaji bhosale Feb 17 '19 at 17:54
  • @shivajibhosale - If there are no other applications on that server, make the buffer_pool 4G when the data gets too big to fit in the 1G. – Rick James Feb 17 '19 at 17:56
  • Yes I changed to 4G. Update: Exist clause not giving expected output in every situation so I reverted it. So now its taking 2seconds to execute the query, and if I remove order by clause it is taking few milliseconds. I am updating the question so please check. – shivaji bhosale Feb 17 '19 at 20:26
  • @shivajibhosale - Before I tackle things, let's discuss isLiked. Is that effectively a true/false value? Or can isLiked be >= 2? – Rick James Feb 17 '19 at 20:40
  • isLiked is kind of true or false it returns 0 or 1. – shivaji bhosale Feb 18 '19 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.