CODEWITHSUNDEEP
mysqlsqlperformance
Sonrimos
Sonrimos

Reputation: 97

Substitute "OR EXISTS" in MySql query so i can get better perfomance results

This query is taking forever to finish in MySql 8, doing some research i found out that the "EXISTS" in this code can be extremely slow in some queries. When i remove the "OR EXISTS" sub-query part, it runs in less than a second.

So i need to substitute the "OR EXISTS" in this query so i can get all the users i need:

SELECT u.name, 
                u.email,
                u.cpf,
                u.register,
                r.name AS role_name,
                s.name AS sector_name,
                b.name AS branch_name,
                u.status
            FROM users u
            INNER JOIN roles r ON r.id = u.role_id
            INNER JOIN sectors s ON s.id = u.sector_id
            INNER JOIN branches b ON b.id = u.branch_id
            WHERE u.status = 2 OR EXISTS (
                SELECT * 
                FROM user_recovery ur 
                WHERE ur.user_id = u.id 
                    AND ur.status_recovery = 1
            )

Is there a way to do it without the "OR EXISTS"?

Upvotes: 1

Views: 125

Answers (2)

Rick James
Rick James

Reputation: 142208

"I'll see your UNION; and raise you a derived table."

SELECT  u.name, 
        u.email,
        u.cpf,
        u.register,
        r.name AS role_name,
        s.name AS sector_name,
        b.name AS branch_name,
        u.status
    FROM (  SELECT id
                FROM users
                WHERE status = 2
            UNION DISTINCT   -- or UNION ALL; see below
            SELECT user_id
                FROM user_recovery 
                WHERE status_recovery = 1  -- see new index
         ) AS u1
    JOIN users AS u  USING(id)  -- self-join to pick up other columns
    JOIN roles r    ON r.id = u.role_id
    JOIN sectors s  ON s.id = u.sector_id
    JOIN branches b ON b.id = u.branch_id;

Indexes:

user_recovery: INDEX(status_recovery, user_id) -- in this order
users: INDEX(status, id) -- in this order
(I assume `id` is the PRIMARY KEY in each table)

The general rule here is... When you have a bunch of JOINs, but a single table that controls which rows, but that is messy or slow (eg UNION in this case, GROUP BY or LIMIT in other cases),

  1. Optimize finding the ids (user.id aka user_id) is the optimal way.
  2. Then JOIN back to the original table (if needed), plus the other tables.

In doing all that, it became apparent that a new index for user_recovery might be beneficial.

(If UNION ALL won't produce any dups, switch to it for a little more speed.)

Upvotes: 0

nbk
nbk

Reputation: 49373

Or can enforce a full scan

try

you can't get rid of the eXISTS clause because it increases the number of returned rows.

Add a INDEX on user status and user_recovery userid,status_recovery and on the on Clause columns.

SELECT  u.name, 
        u.email,
        u.cpf,
        u.register,
        r.name AS role_name,
        s.name AS sector_name,
        b.name AS branch_name,
        u.status
    FROM users u
    INNER JOIN roles r ON r.id = u.role_id
    INNER JOIN sectors s ON s.id = u.sector_id
    INNER JOIN branches b ON b.id = u.branch_id
    WHERE u.status = 2 
UNION
SELECT  u.name, 
    u.email,
    u.cpf,
    u.register,
    r.name AS role_name,
    s.name AS sector_name,
    b.name AS branch_name,
    u.status
FROM users u
INNER JOIN roles r ON r.id = u.role_id
INNER JOIN sectors s ON s.id = u.sector_id
INNER JOIN branches b ON b.id = u.branch_id
WHERE EXISTS (
    SELECT 1 
    FROM user_recovery ur 
    WHERE ur.user_id = u.id 
        AND ur.status_recovery = 1
)

Upvotes: 1

Related Questions