MySQL Forums
Forum List  »  Performance

Help improving performance on JOIN query
Posted by: Jason Carlton
Date: November 22, 2021 01:54PM

I'm working with a table for user's Private Messages. It is 2.5G in size (2.1G data, 433M index) and has 10.4 million rows.

One column is "folder", which is VARCHAR(50). I have 3 built-in folders ("inbox", "sent", "deleted"), but the user can create as many folders as they want to organize their messages.

Some users have a LOT of messages, which makes their initial SELECT query run slower than I'd like (more than 5 seconds). As far as I can tell, the bottleneck comes from the JOIN.

Here is the one of the queries in the slow log:

# Query_time: 6.225232 Lock_time: 0.000122 Rows_sent: 21 Rows_examined: 8366
# Rows_affected: 0 Bytes_sent: 23616
SET timestamp=1636605307;
SELECT private_messages.id, username, folder, status, postdate, sender, subject, message, pm_img.image
FROM private_messages
LEFT JOIN pm_img
ON pm_img.id = private_messages.id
WHERE
username='example' AND
folder='inbox' AND
(status = "New" OR status = "" OR status IS NULL) AND
COALESCE(pm_img.sorter,0) = 0
ORDER BY postdate DESC
LIMIT 21;


I added the "status" condition because the index includes "status", but it had no actual impact.

In an attempt to improve the speed I mashed the query to this:

SELECT private_messages.id, status, postdate, sender, subject, message, pm_img.image
FROM private_messages
LEFT JOIN pm_img
ON pm_img.id = private_messages.id AND
pm_img.sorter = 0
WHERE
username='example' AND
folder='inbox'
ORDER BY postdate DESC
LIMIT 21;


but it still takes 5-7 seconds.

The indexes being used are:

On private_messages:
username - VARCHAR(50)
folder - VARCHAR(50)
status - ENUM('New', NULL)
postdate - VARCHAR(14), I'll be changing to TIMESTAMP in the near future

On pm_img (UNIQUE):
id - INT(11)
sorter - TINYINT(2)

I THINK that what's happening is that it's reading all 8,366 rows, going through pm_img to find matches for all of them, and then returning 21 results.

Any suggestions on improving the response time?

Options: ReplyQuote


Subject
Views
Written By
Posted
Help improving performance on JOIN query
145
November 22, 2021 01:54PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.