MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.23


CREATE TABLE log_table (
  `user_id` VARCHAR(5),
  `date_time` DATETIME,
  `event_name` VARCHAR(10),
  `trivial` int
);

INSERT INTO log_table
  (`user_id`, `date_time`, `event_name`, `trivial`)
VALUES
  ('001', '2020-12-10 10:00:02', 'c', 3),
  ('001', '2020-12-10 10:00:01', 'b', 9),
  ('001', '2020-12-10 10:00:40', 'e', 2),
  ('001', '2020-12-10 10:00:20', 'd', 6),
  ('001', '2020-12-10 10:00:00', 'a', 1),
  ('002', '2020-12-09 10:00:10', 'C', 9),
  ('002', '2020-12-10 10:00:50', 'D', 0),
  ('002', '2020-12-10 10:00:02', 'A', 2),
  ('002', '2020-12-10 10:00:09', 'B', 4);
#using LEFT JOIN
    SELECT t_left.*
    FROM   log_table AS t_left
           LEFT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    AND  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    
    SELECT t_left.*
    FROM   log_table AS t_left
           LEFT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    WHERE  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;


user_id
date_time
event_name
trivial


002
2020-12-09 10:00:10
C
9

001
2020-12-10 10:00:00
a
1

001
2020-12-10 10:00:01
b
9

001
2020-12-10 10:00:02
c
3

002
2020-12-10 10:00:02
A
2

002
2020-12-10 10:00:09
B
4

001
2020-12-10 10:00:20
d
6

001
2020-12-10 10:00:40
e
2

002
2020-12-10 10:00:50
D
0




user_id
date_time
event_name
trivial


002
2020-12-10 10:00:02
A
2

002
2020-12-10 10:00:09
B
4

002
2020-12-10 10:00:50
D
0


#using RIGHT JOIN
    SELECT t_left.*
    FROM   log_table AS t_left
           RIGHT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    AND  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    
    SELECT t_left.*
    FROM   log_table AS t_left
           RIGHT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    WHERE  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;


user_id
date_time
event_name
trivial


002
2020-12-10 10:00:02
A
2

002
2020-12-10 10:00:09
B
4

002
2020-12-10 10:00:50
D
0




user_id
date_time
event_name
trivial


002
2020-12-10 10:00:02
A
2

002
2020-12-10 10:00:09
B
4

002
2020-12-10 10:00:50
D
0


    explain
    SELECT t_left.*
    FROM   log_table AS t_left
           LEFT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    AND  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    explain
    SELECT t_left.*
    FROM   log_table AS t_left
           LEFT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    WHERE  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    explain
    SELECT t_left.*
    FROM   log_table AS t_left
           RIGHT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    AND  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    explain
    SELECT t_left.*
    FROM   log_table AS t_left
           RIGHT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    WHERE  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
t_left
null
ALL
null
null
null
null
9
100.00
Using temporary; Using filesort

1
PRIMARY
<derived2>
null
ALL
null
null
null
null
1
100.00
Using where; Using join buffer (hash join)

2
DERIVED
log_table
null
ALL
null
null
null
null
9
100.00
Using where




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
system
null
null
null
null
1
100.00
Using filesort

1
PRIMARY
t_left
null
ALL
null
null
null
null
9
11.11
Using where

2
DERIVED
log_table
null
ALL
null
null
null
null
9
100.00
Using where




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
system
null
null
null
null
1
100.00
Using temporary; Using filesort

1
PRIMARY
t_left
null
ALL
null
null
null
null
9
100.00
Using where

2
DERIVED
log_table
null
ALL
null
null
null
null
9
100.00
Using where




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
system
null
null
null
null
1
100.00
Using filesort

1
PRIMARY
t_left
null
ALL
null
null
null
null
9
11.11
Using where

2
DERIVED
log_table
null
ALL
null
null
null
null
9
100.00
Using where


Alter table log_table add index user_id(user_id);
explain
    SELECT t_left.*
    FROM   log_table AS t_left
           LEFT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    AND  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    explain
    SELECT t_left.*
    FROM   log_table AS t_left
           LEFT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    WHERE  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    explain
    SELECT t_left.*
    FROM   log_table AS t_left
           RIGHT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    AND  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    explain
    SELECT t_left.*
    FROM   log_table AS t_left
           RIGHT JOIN (SELECT user_id,
                              date_time
                       FROM   log_table
                       WHERE  BINARY event_name = 'B'
                       LIMIT  1) AS t_right
                   ON t_left.user_id = t_right.user_id
    WHERE  date(t_left.date_time) = date(t_right.date_time)
    ORDER  BY date_time;
    


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
t_left
null
ALL
null
null
null
null
9
100.00
Using temporary; Using filesort

1
PRIMARY
<derived2>
null
ALL
null
null
null
null
1
100.00
Using where; Using join buffer (hash join)

2
DERIVED
log_table
null
ALL
null
null
null
null
9
100.00
Using where




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
system
null
null
null
null
1
100.00
Using filesort

1
PRIMARY
t_left
null
ref
user_id
user_id
23
const
4
100.00
Using where

2
DERIVED
log_table
null
ALL
null
null
null
null
9
100.00
Using where




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
system
null
null
null
null
1
100.00
Using temporary; Using filesort

1
PRIMARY
t_left
null
ref
user_id
user_id
23
const
4
100.00
Using where

2
DERIVED
log_table
null
ALL
null
null
null
null
9
100.00
Using where




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
system
null
null
null
null
1
100.00
Using filesort

1
PRIMARY
t_left
null
ref
user_id
user_id
23
const
4
100.00
Using where

2
DERIVED
log_table
null
ALL
null
null
null
null
9
100.00
Using where
user_id	date_time	event_name	trivial
002	2020-12-09 10:00:10	C	9
001	2020-12-10 10:00:00	a	1
001	2020-12-10 10:00:01	b	9
001	2020-12-10 10:00:02	c	3
002	2020-12-10 10:00:02	A	2
002	2020-12-10 10:00:09	B	4
001	2020-12-10 10:00:20	d	6
001	2020-12-10 10:00:40	e	2
002	2020-12-10 10:00:50	D	0
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t_left	null	ALL	null	null	null	null	9	100.00	Using temporary; Using filesort
1	PRIMARY	<derived2>	null	ALL	null	null	null	null	1	100.00	Using where; Using join buffer (hash join)
2	DERIVED	log_table	null	ALL	null	null	null	null	9	100.00	Using where