Help improving performance on JOIN query
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?