Very slow query when combining order by id desc and not exists

Question

SELECT
    *
FROM
    `users`
WHERE
    `id` != 1
    AND `users`.`activated` = 1
    AND NOT EXISTS (
        SELECT
            1
        FROM
            `blockings`
        WHERE (blockings.user_id = users.id
            AND blockings.blocked_id = 1)
        OR(blockings.blocked_id = users.id
            AND blockings.user_id = 1))
ORDER BY
    users.id DESC
LIMIT 10 OFFSET 0

It takes 5 seconds to complete this query on a 30k rows table

It takes a moment to complete when I remove NOT EXISTS part

There is Index on users.id and blockings.user_id and blockings.blocked_id

How can I speed up this query?

Answer 1

This is a bit of a "stab in the dark" as you have not included your table definitions or EXPLAIN output for your current query in your question.

With single column indices on blockings.user_id and blockings.blocked_id you should be seeing an index_merge for blockings in the EXPLAIN output for your current query.

With PK (user_id, blocked_id) and an index on (blocked_id, user_id) using a UNION in your NOT EXISTS is likely to be much faster:

SELECT *
FROM users u
WHERE u.id != 1
  AND u.activated = 1
  AND NOT EXISTS (
        SELECT 1
        FROM blockings b
        WHERE b.user_id = u.id AND b.blocked_id = 1
        UNION ALL
        SELECT 1
        FROM blockings b
        WHERE b.blocked_id = u.id AND b.user_id = 1
    )
ORDER BY u.id DESC
LIMIT 10 OFFSET 0;

As suggested by MatBailie, it is worth trying with the UNION ALL split into two separate NOT EXISTS:

SELECT *
FROM users u
WHERE u.id != 1
AND u.activated = 1
AND NOT EXISTS (
    SELECT 1
    FROM blockings b
    WHERE b.user_id = u.id AND b.blocked_id = 1
)
AND NOT EXISTS (
    SELECT 1
    FROM blockings b
    WHERE b.blocked_id = u.id AND b.user_id = 1
)
ORDER BY u.id DESC
LIMIT 10 OFFSET 0;