1

I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it:

SELECT 
   events.*, 
   (SELECT MAX(updated_at) 
    FROM   events e 
    WHERE  e.user_id = events.user_id 
           AND type = 'follow' 
           AND e.updated_at < events.updated_at 
           AND events.type = 'unfollow'
   ) AS last_follow_date 
FROM   events 
WHERE  user_id = 1; 

Database structure:

CREATE TABLE `events` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    `user_id` int(10) unsigned NOT NULL,
    `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `follower` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `follower_pk` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    KEY `events_user_id_foreign` (`user_id`),
    CONSTRAINT `events_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Some data:

INSERT INTO `users` (`id`, `name`, `email`)
VALUES (1, 'Aydin Hassan', 'email@website.com');

INSERT INTO `events` (`id`, `created_at`, `updated_at`, `user_id`, `type`, `follower`, `follower_pk`)
VALUES 
(1, '2015-01-01 00:00:00', '2015-01-01 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(2, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'unfollow', 'annie.leibovitz', '10'),
(3, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'follow', 'edward.weston', '11'),
(5, '2015-10-10 00:00:00', '2015-10-10 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(4, '2017-01-01 10:10:00', '2017-01-01 10:10:00', 1, 'unfollow', 'annie.leibovitz', '10');

The table stores events regarding followers of a user on a makeshift social platform. The query is adding (if the event is a unfollow type) the last date that the follower started following the user.

The correct results should be:

+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
| id |     created_at      |     updated_at      | user_id |   type   |    follower     | follower_pk |  last_follow_date   |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
|  1 | 2015-01-01 00:00:00 | 2015-01-01 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
|  2 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-01-01 00:00:00 |
|  3 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | follow   | edward.weston   |          11 | NULL                |
|  4 | 2017-01-01 10:10:00 | 2017-01-01 10:10:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-10-10 00:00:00 |
|  5 | 2015-10-10 00:00:00 | 2015-10-10 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+

As a bonus question: Is there maybe a better way to store and query this data?

2

You can get what you want with the following JOINed query, which ressembles very much your original one:

SELECT
    e.id, e.created_at, e.updated_at, e.user_id, e.type, e.follower, e.follower_pk, 
    max(e2.updated_at) AS last_follow_date
FROM
    events e
    LEFT JOIN events e2 
        ON      e2.user_id = e.user_id 
            AND e2.type = 'follow' 
            AND e2.updated_at < e.updated_at
            AND e.type = 'unfollow' 
WHERE
    e.user_id = 1
GROUP BY
    e.id 
ORDER BY
    e.id, e.updated_at ;

With regard to performance, your queries will benefit from an index such as:

-- Index to improve performance
CREATE INDEX idx_events_user_id_type_updated_at
    ON events (user_id, type, updated_at) ;

The three columns user_id, type and updated_at appear in the JOIN condition. The order in which you want them is user_id (you make an = comparison, and is supposed to have high cardinality --i.e.: very many values--), type (you make an = comparison, cardinality = 2), updated_at checked with an inequality... MySQL is not good enough to use it as part of the index seek, but will make the index a covering one, and that will save from checking the original table.


Alternative: a slight twist on your original query, might save some time:

SELECT 
   e1.*, 
   case when type = 'unfollow' then
      (SELECT MAX(e2.updated_at) 
       FROM   events e2 
       WHERE  
                  e2.user_id = e1.user_id 
              AND e2.type = 'follow'
              AND e2.updated_at < e1.updated_at
       )
   else NULL end AS last_follow_date
FROM   events e1
WHERE  e1.user_id = 1; 

You can check everything at dbfiddle here

1

This will probably benefit all variants in the Question and Answer:

INDEX(type, user_id, updated_at)

The problem smells like Groupwise-max .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.