JOIN alternatives to Sub Query

Question

I'm trying to show a logged in user the number of notifications that they haven't seen yet each time they logged in. To that end I have the following query:

"select count(eventId) as count from notifications where updateId in("
 # 2-the updates NOT IN what the user has seen
 "select updateId from updates where updateId not in("
 # 1-the updates the user has seen
 "select updateId from seen_updates where memberId={}))".format(memberId))

The problem is this seems to take a while as the number of notifications increases. Is there a way to do this better with a join?


mysql> describe notifications;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| eventId  | int(10) unsigned | NO   |     | NULL    |       |
| updateId | int(10) unsigned | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe updates;
+---------------+------------------+------+-----+-------------------+----------------+
| Field         | Type             | Null | Key | Default           | Extra          |
+---------------+------------------+------+-----+-------------------+----------------+
| updated       | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
| updateId      | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| eventCategory | varchar(40)      | YES  |     | NULL              |                |
| eventsCount   | int(10) unsigned | NO   |     | NULL              |                |
+---------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

mysql> describe seen_updates;
+----------+------------------+------+-----+-------------------+-------+
| Field    | Type             | Null | Key | Default           | Extra |
+----------+------------------+------+-----+-------------------+-------+
| updateId | int(10) unsigned | NO   |     | NULL              |       |
| memberId | int(10) unsigned | NO   |     | NULL              |       |
| updated  | timestamp        | NO   |     | CURRENT_TIMESTAMP |       |
+----------+------------

This seems to be python code or something. Either tag it with that, or reduce your code to pure SQL. — trincot
Thanks for reply @trincot I've added the tables so you could see it better. — stingMantis

trincot trincot · Accepted Answer · 2017-05-27T14:22:55

With joins you could do it like this:

select     count(eventId) as count 
from       notifications 
inner join updates 
        on updates.updateId = notifications.updateId
left join  seen_updates 
        on seen_updates.updateId = notifications.updateId
       and seen_updates.memberId = {}
where      seen_updates.updateId is null

Depending on your data structure, you might even not need the clause with inner join updates. It seems logical that an notification.updateId needs to have an existing counterpart in the updates table. Only if this is not guaranteed that inner join needs to stay.

JOIN alternatives to Sub Query

2 Answers