mysql: How to exclude rows from table which exist in table_alias with good perfomanse?

Question

I've sql with NOT EXIST and it works very slowly in big db:

SELECT *
FROM
    (
        SELECT * FROM profiles ORDER BY id DESC
        /* I need this order HERE! More info: https://stackoverflow.com/q/43516402/2051938 */
    ) AS users
WHERE
    NOT EXISTS (
        SELECT *
        FROM request_for_friendship
        WHERE
            (
                request_for_friendship.from_id = 1
                AND
                request_for_friendship.to_id = users.id
            )
            OR
            (
                request_for_friendship.from_id = users.id
                AND
                request_for_friendship.to_id = 1
            )
    )
LIMIT 0 , 1;

And I think I need to get request_for_friendship with some WHERE and after that check NOT EXIST, like this:

SELECT users.*
FROM
    (
        SELECT * FROM profiles ORDER BY id DESC
    ) AS users,
    (
        SELECT *
        FROM request_for_friendship
        WHERE
            request_for_friendship.from_id = 1
            OR
            request_for_friendship.to_id = 1
    ) AS exclude_table
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM exclude_table /* #1146 - Table 'join_test.exclude_table' doesn't exist */
        WHERE
            request_for_friendship.from_id = users.id
            OR
            request_for_friendship.to_id = users.id
    )
LIMIT 0 , 1;

But it doesn't work: #1146 - Table 'join_test.exclude_table' doesn't exist

My tables:

1) profiles

+----+---------+
| id | name    |
+----+---------+
|  1 | WILLIAM |
|  2 | JOHN    |
|  3 | ROBERT  |
|  4 | MICHAEL |
|  5 | JAMES   |
|  6 | DAVID   |
|  7 | RICHARD |
|  8 | CHARLES |
|  9 | JOSEPH  |
| 10 | THOMAS  |
+----+---------+

2) request_for_friendship

+----+---------+-------+
| id | from_id | to_id |
+----+---------+-------+
|  1 |       1 |     2 |
|  2 |       1 |     3 |
|  3 |       1 |     8 |
|  5 |       4 |     1 |
|  6 |       9 |     1 |
+----+---------+-------+

How to do some like this or better for perfomance?

p.s. I need to get only 1 row from table

Demo: http://rextester.com/DTA64368

I've already tried LEFT JOIN, but I've problem with order with him. mysql: how to save ORDER BY after LEFT JOIN without reorder?

@jarlh more info about rand in subquery in my sql: http://stackoverflow.com/a/43525545/2051938 — mixalbl4, Apr 28 '17 at 13:00
and why the subqueries, and why the * in the exists use 1 so it doesn't have to return the entire record. as the selected value is immaterial it's the join that eliminates/keeps data. — xQbert, Apr 28 '17 at 13:00

score 0 · Answer 1 · answered Apr 28 '17 at 12:56

0

First, do not use subqueries unnecessarily. Second, split the NOT EXISTS into two conditions:

SELECT p.*
FROM profiles p
WHERE NOT EXISTS (SELECT 1
                  FROM request_for_friendship rff
                  WHERE rff.from_id = 1 AND
                        rff.to_id = p.id
                 ) AND
      NOT EXISTS (SELECT 1
                  FROM request_for_friendship rff
                  WHERE rff.to_id = 1 AND
                        rff.from_id = p.id
                 )
ORDER BY id DESC;

This can now make use of two indexes: request_for_friendship(to_id, from_id) and request_for_friendship(from_id, to_id). Each index is needed for one of the NOT EXISTS conditions.

answered Apr 28 '17 at 12:56

Gordon Linoff

1,242,037
58
646
786

I need to use subqueries (I've added mode info in the post) – mixalbl4 Apr 28 '17 at 13:06
With 1 `NOT EXIST` == 2.6 sec, with splitted `NOT EXIST` == 3.2 sec – mixalbl4 Apr 28 '17 at 13:13
@MixerOID . . . Do you have the two indexes described in the answer? – Gordon Linoff Apr 29 '17 at 13:52
Which version? Some newer versions may run faster. – Rick James Apr 30 '17 at 00:49
@GordonLinoff yep, I've 2 indexes. – mixalbl4 Apr 30 '17 at 07:33
@RickJames latest Mysql 5.7 – mixalbl4 Apr 30 '17 at 07:33
"2 indexes" may not be as good as "1 composite index" -- _show us_ the index(es). – Rick James Apr 30 '17 at 18:27

score 0 · Answer 2 · edited Apr 30 '17 at 00:47

0

I still think there's ways to optimize this as 'in' is generally slower.

SELECT * 
FROM profiles p
WHERE NOT EXISTS (SELECT 1
                  FROM request_for_friendship
                  WHERE (request_for_friendship.from_id,
                         request_for_friendship.to_id)
                     in ((1,p.id),
                         (p.id,1))
                 )

edited Apr 30 '17 at 00:47

Rick James

135,179
13
127
222

answered Apr 28 '17 at 12:59

xQbert

34,733
2
41
62

in big db it takes > 5 minutes – mixalbl4 Apr 28 '17 at 13:33
Nifty syntax, but rotten performance. The Optimizer fails to do what it ought to do. – Rick James Apr 30 '17 at 00:54

score 0 · Answer 3 · answered Apr 30 '17 at 01:01

Get rid of the id in request_for_friendship. It wastes space and performance. The table has a "natural" PRIMARY KEY, which I will get to in a moment.

Since it seems that the relationship seems to commutative, let's make use of that by sorting the from and to -- put the smaller id in from and the larger is to. See LEAST() and GREATEST() functions.

Then you need only one EXISTS(), not two. And have

PRIMARY KEY(from_id, to_id)

Now to rethink the purpose of the query... You are looking for the highest id that is not "related" to id #1, correct? That sounds like a LEFT JOIN.

SELECT 
    FROM profiles AS p
    LEFT JOIN request_for_friendship AS r  ON r.to = p.id AND r.from = 1
    WHERE r.to IS NULL
    ORDER BY id DESC
    LIMIT 1;

This may run about the same speed as the EXISTS -- Both walk through profiles from the highest id, reaching into the other table to see if a row is there.

If there is no such id, then the entire profiles table will be scanned, plus a the same number of probes into the other table.

`LEFT JOIN` runs faster then `EXISTS` but it have some bug: https://stackoverflow.com/questions/43678470/mysql-how-to-save-order-by-after-left-join-without-reorder — mixalbl4, Apr 30 '17 at 07:36

mysql: How to exclude rows from table which exist in table_alias with good perfomanse?

3 Answers3