CODEWITHSUNDEEP
mysqlsqldatabaseindexing
TIMEX
TIMEX

Reputation: 271824

In MySQL, how do I query "last inserted row equals"?

mysql> desc accounts_users;
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | int(11)      | NO   | PRI | NULL    | auto_increment |
| login_at               | datetime     | YES  |     | NULL    |                |
| logout_at              | datetime     | YES  |     | NULL    |                |
+------------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc rooms_events;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)      | NO   | MUL | NULL    |                |
| event      | varchar(255) | NO   | MUL | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

When a user performs an action, I add an "event" row to rooms_events table. The event column could be "create" or "destroy". There are no unique columns.

I want to query:

Find all unique user_ids whose latest event is "create" AND hasn't logged in (login_at) for 24 hours.

How do I perform such a query?

Upvotes: 0

Views: 63

Answers (2)

Bjoern
Bjoern

Reputation: 16304

I'd say it could be queried like this:

SELECT DISTINCT U.id
FROM    accounts_users U
INNER JOIN rooms_events E ON U.id = E.user_id
WHERE   U.login_at < DATE_SUB(NOW(),INTERVAL 24 HOUR)
        AND E.created_at IN (
            SELECT MAX(created_at)
            FROM rooms_events
            GROUP BY user_id
           )
        AND E.`event` = 'create';

I'm not 100% sure the subquery does its job in every condition possible, but with some test data it worked on my machine.

Upvotes: 1

Rick James
Rick James

Reputation: 142298

SELECT user_id
    FROM rooms_events c
    WHERE NOT EXISTS (
                 SELECT *
                     FROM room_events
                     WHERE event = 'create'
                       AND user_id = c.user_id
                       AND created_at > c.created_at )
      AND NOT EXISTS (
                 SELECT *
                     FROM accounts_users
                     WHERE id = c.user_id
                       AND login_at > NOW() - INTERVAL 24 HOUR;

Index:

room_events:  INDEX(event, user_id, created_at)

This assumes that created_at is the time of the event.

Since you seem to record only the latest login, that can be simplified to:

SELECT u.id
    FROM rooms_events c
    JOIN account_users u  ON c.user_id = u.id
    WHERE u.login_at < NOW() - INTERVAL 24 HOUR
      AND NOT EXISTS (
                 SELECT *
                     FROM room_events
                     WHERE event = 'create'
                       AND user_id = c.user_id
                       AND created_at > c.created_at )

If you expect to have a lot of old data lying around, then INDEX(login_at, id) could be beneficial.

If event can be only two values, consider ENUM('create', 'destroy') NOT NULL.

For room_events, won't (user_id, event) be unique? Hence it could be the PRIMARY KEY (and get rid of id). If you make that change, then my suggested index is not needed.

Upvotes: 0

Related Questions