Edit Question - KnowledgeHub

Title Body

The following query is intended to receive a list of unread messages by user. It involves 3 tables: recipients contains a relation of users to message IDs, messages contains the messages themselves, and message_readers contains a list of which users have read which messages. The query reliably takes 4.9 seconds - this is seriously hurting our performance, and is especially worrisome since we hope the database will eventually be several orders of magnitude larger. Granted, it's an inherently heavy query, but the data set is tiny, and intuitively it seems that it should be much faster. The server has enough memory (32gb) that the entire database should be loaded in RAM at all times, and there's nothing else running on the box. The tables are all tiny: recipients: 23581 messages: 9679 message_readers: 2685 The query itself: SELECT m.* FROM messages m INNER JOIN recipients r ON r.message_id = m.id LEFT JOIN message_readers mr ON mr.message_id = m.id WHERE r.id = $user_id AND (mr.read_by_id IS NULL OR mr.read_by_id <> $user_id) The explain plan is pretty straightforward: +----+-------------+-------+--------+-----------------------------------+-----------------------------------+---------+--------------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------------------+-----------------------------------+---------+--------------------------------+-------+-------------+ | 1 | SIMPLE | r | ref | index_recipients_on_id | index_recipients_on_id | 768 | const | 11908 | Using where | | 1 | SIMPLE | m | eq_ref | PRIMARY

Tags (comma-separated) Cancel