Question 20087 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    Here is a possible alternative query and you may need to add index on the columns used in the where clause.

    SELECT
    users.id,  /* all the necessary columns goes here */
    users.name,
    users. Email
FROM
    users
    LEFT JOIN blockings ON (
        (blockings.user_id = users.id AND blockings.blocked_id = 1)
        OR (blockings.blocked_id = users.id AND blockings.user_id = 1)
    )
WHERE
    users.id != 1
    AND users.activated = 1
    AND blockings.id IS NULL
ORDER BY
    users.id DESC
LIMIT 10 OFFSET 0;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search