Get data from mysql, taking almost 436 seconds

Question

I am android developer and don't have much experience in mysql. I have 3 tables in mysql database.

First table :-UserTable

+-------------+-------------+----------+----------------------+
| user_id     | name      |   image    |   joining_date       |
+-------------+-------------+----------+----------------------+
|         100 | King      | defualt.jpg | 2018-05-23 20:09:27 |
|         101 | Kochhar   | defualt.jpg | 2018-05-23 20:09:27 |
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |

Second Table :- friendRequests

+-------------+-------------+----------+
| unique_id   | from_id     |   to_id  |
+-------------+-------------+----------+
|         1  | 100          | 101      |
|         2  | 200          | 110      |
| - - - - - - - - - - - - - - - - - - -|
| - - - - - - - - - - - - - - - - - - -|

Third Table :- friends

+-------------+-------------+----------+
| unique_id   | from_id     |   to_id  |
+-------------+-------------+----------+
|         1  | 104          | 101      |
|         2  | 206          | 110      |
| - - - - - - - - - - - - - - - - - - -|
| - - - - - - - - - - - - - - - - - - -|

I want to get the list of users who are not friends to me and also we have not pending friend request. So i tried with following query:-

SELECT usertable.name,
       usertable.user_id,
       usertable.image
FROM   `UserTable` AS usertable
WHERE  usertable.user_id <> '100'
       AND (SELECT Count(*)
            FROM   friendrequests
            WHERE  ( to_id = '100'
                     AND from_id = usertable.user_id )
                    OR ( to_id = usertable.user_id
                         AND from_id = '100' )) = 0
       AND (SELECT Count(*)
            FROM   friends
            WHERE  to_id = usertable.user_id
                   AND from_id = '100'
                    OR to_id = '100'
                       AND from_id = usertable.user_id) = 0
ORDER  BY usertable.joining_date DESC
LIMIT  10

It is working but takes 436 seconds. What is the proper way to write this query?

`user` is a Keyword. It is not a good candidate for table name. You should really name your table to something else. — Madhur Bhaiya, Oct 24 '18 at 08:44
Avoid to use select in your select and try to use some join maybe? — Mickaël Leger, Oct 24 '18 at 09:02
Can you five us a SQL fiddle please with your data to test some request? — Mickaël Leger, Oct 24 '18 at 09:17
Smells like you don't have any indexes. Please provide `SHOW CREATE TABLE`. — Rick James, Oct 25 '18 at 17:46

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Here is some other way to do it :

1) Using NOT IN

You add a condition on usertable.user_id that should'nt be in a list of id : those id are all the id of the table friendrequests and friends that have to_id or from_id = 100.

SELECT 
  usertable.name,
  usertable.user_id,
  usertable.image
FROM `UserTable` AS usertable
WHERE usertable.user_id <> '100'
  AND usertable.user_id NOT IN 
    (SELECT from_id as user_id FROM friendrequests WHERE to_id = '100'
     UNION
     SELECT to_id as user_id FROM friendrequests WHERE from_id = '100'
     UNION
     SELECT from_id as user_id FROM friends WHERE to_id = '100'
     UNION
     SELECT to_id as user_id FROM friends WHERE from_id = '100')               
ORDER  BY usertable.joining_date DESC
LIMIT  10

EDIT according to Rick James comment, same with no union:

SELECT 
  usertable.name,
  usertable.user_id,
  usertable.image
FROM `UserTable` AS usertable
WHERE usertable.user_id <> '100'
  AND usertable.user_id NOT IN (SELECT from_id as user_id FROM friendrequests WHERE to_id = '100')
  AND usertable.user_id NOT IN (SELECT to_id as user_id FROM friendrequests WHERE from_id = '100')
  AND usertable.user_id NOT IN (SELECT from_id as user_id FROM friends WHERE to_id = '100')
  AND usertable.user_id NOT IN (SELECT to_id as user_id FROM friends WHERE from_id = '100')               
ORDER  BY usertable.joining_date DESC
LIMIT  10

2) Using a LEFT JOIN

I saw this answer and tried to use the same logic : you make some LEFT JOIN with the other table and add a condition where those join return NULL value

SELECT 
  usertable.name,
  usertable.user_id,
  usertable.image
FROM `UserTable` AS usertable
LEFT JOIN friendrequests as FRa on FRa.from_id = usertable_user_id
LEFT JOIN friendrequests as FRb on FRb.to_id   = usertable_user_id
LEFT JOIN friends        as Fa  on Fa.from_id  = usertable_user_id
LEFT JOIN friends        as Fb  on Fb.to_id    = usertable_user_id
WHERE usertable.user_id <> '100' 
  AND FRa.from_id IS NULL
  AND FRb.to_id IS NULL
  AND Fa.from_id IS NULL
  AND Fb.to_id IS NULL

3) Using NOT EXISTS

SELECT 
  usertable.name,
  usertable.user_id,
  usertable.image
FROM `UserTable` AS usertable
WHERE usertable.user_id <> '100'
  AND NOT EXISTS (SELECT from_id as user_id FROM friendrequests WHERE to_id = '100')
  AND NOT EXISTS (SELECT to_id as user_id FROM friendrequests WHERE from_id = '100')
  AND NOT EXISTS (SELECT from_id as user_id FROM friends WHERE to_id = '100')
  AND NOT EXISTS (SELECT to_id as user_id FROM friends WHERE from_id = '100')               
ORDER  BY usertable.joining_date DESC
LIMIT  10

Sorry but with no sample data or SQL Fiddle to test those query I can't tell you if it return what you want or if it's really faster...

EDIT : This page is from 2009 so the result have change since ! I won't erase it since you still have the query structure I used before :

I recommand you to looks this page to understand the difference between those queries and give you some idea to achieve what you want : https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

A third way is proposed in this page (using NOT EXISTS) but according to the test it's not efficient :

This query, however, is a little bit less efficient than the previous two: it takes 0.92 s.

This is not much of a performance drop, however, the query takes 27% more time.

A lot of effort has gone into the Optimizer since 2009, especially in the areas being discussed here. Don't trust that link. — Rick James, Oct 25 '18 at 17:49
Try to avoid `UNION`; it has some overhead and avoids some optimization paths. Try the `NOT IN` solution with fpir `AND .. NOT IN ( SELECT ... )` clauses. And do the equivalent with `NOT EXISTS`: four `AND NOT EXISTS ( SELECT 1 FROM ... )` clauses. — Rick James, Oct 25 '18 at 18:02
Thanks. Perhaps gourav is running an old version? You have a timing--are you speaking for him? Or did you run an independent test? — Rick James, Oct 26 '18 at 16:43
I just tried to give him some other way to achieve what he wanted, I don't know how looks his database (index or other thing) so hard to make a test — Mickaël Leger, Oct 26 '18 at 17:43

Get data from mysql, taking almost 436 seconds

1 Answers1