Multi SELECT query for multiple ids

Question

I do a SELECT to decide if I send a notification to a user. (10.3.23-MariaDB)

First part checks if there are 0 unread notifications, if 0 no further SELECT is needed.

If not, I count how many notifications the user has since last equal notification (in this case there always one to count from) and check it against unread ones number user_sess.unread_noti

SELECT 'yes' AS do_insert 
  FROM 
     (SELECT unread_noti 
        FROM user_sess 
      WHERE user_id = 100)user_sess 
 WHERE user_sess.unread_noti = 0 
    OR (SELECT COUNT(*) FROM notification
            WHERE user_id=100 AND id > 400
       ) >= user_sess.unread_noti

Now I want to check multiple users for the same notification, and make it more efficient by doing the SELECT for multiple users at ones, for example something like this (INCORRECT example):

SELECT  user_sess.user_id
    FROM  user_sess
    LEFT JOIN  notification  ON user_sess.unread_noti > 0
      AND  notification.id > 400
      AND  notification.user_id=user_sess.user_id
    WHERE  user_sess.user_id IN (100,101)
      AND  ( user_sess.unread_noti = 0
              OR  COUNT(notification.id) >= user_sess.unread_noti
           )

The notification.id to count from, can be the same for multiple users because I INSERT them in bulk, so they will occupy the same "spot".

notification:
id        Primary   int(11) 
user_id   Index     int(11) 

user_sess:
user_id     Primary int(11)
unread_noti         tinyint(3)

Oh dear :-( Please stop, and see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query instead — Strawberry, Jan 18 '21 at 22:47

score 1 · Accepted Answer · answered Jan 18 '21 at 23:05

I suggest you try to do it in 2 SELECTs, regardless of the number of userids.

The one would get all the userids WHERE unread_noti = 0.

The other would get those that have unread_noti != 0 AND ...

I assume there is an issue that the table is being modified by other threads? So, use a transaction:

BEGIN;
SELECT ... WHERE unread_noti = 0          FOR UPDATE;
SELECT ... WHERE unread_noti != 0 AND ... FOR UPDATE;
...
INSERT ...
COMMIT;

Why?

OR does not optimize well
FOR UPDATE may solve any concurrency issues
It is easier to think through each SELECT separately.

score 1 · Answer 2 · answered Jan 19 '21 at 02:02

You can use group by and having as follows:

SELECT u.user_id, 'yes' AS do_insert 
  FROM user_sess u 
  LEFT JOIN notification N ON u.user_id = n.user_id AND n.id > 400
 Where u.user_id in (100, 101)
Group by u.user_id, u.unread_noti
Having max(u.unread_noti) = 0 OR count(N.USER_ID) > u.unread_noti

Multi SELECT query for multiple ids

2 Answers2