JOIN alternatives to Sub Query

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 |       |
+----------+------------

---

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.

链接地址: http://www.djcxy.com/p/25096.html

上一篇: 何时使用日期时间或时间戳

下一篇: 加入子查询的替代方案