StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs MySQL naturally slow at this kind of query, or do I have it misconfigured?
text
Body
copied!<p>The following query is intended to receive a list of unread messages by user. It involves 3 tables: <code>recipients</code> contains a relation of users to message IDs, <code>messages</code> contains the messages themselves, and <code>message_readers</code> contains a list of which users have read which messages.</p> <p>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.</p> <p>The tables are all tiny: </p> <pre><code>recipients: 23581 messages: 9679 message_readers: 2685 </code></pre> <p>The query itself:</p> <pre><code>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) </code></pre> <p>The explain plan is pretty straightforward:</p> <pre><code>+----+-------------+-------+--------+-----------------------------------+-----------------------------------+---------+--------------------------------+-------+-------------+ | 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 | PRIMARY | 4 | db.r.message_id | 1 | Using index | | 1 | SIMPLE | mr | ALL | NULL | NULL | NULL | NULL | 2498 | Using where | +----+-------------+-------+--------+-----------------------------------+-----------------------------------+---------+--------------------------------+-------+-------------+ </code></pre> <p>There IS an index on <code>message_readers.read_by_id</code>, but I guess it can't really use it because of the IS NULL condition.</p> <p>I'm using all default settings except for the following:</p> <pre><code>key_buffer=4G query_cache_limit = 256M query_cache_size = 1G innodb_buffer_pool_size=12G </code></pre> <p>Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload