Will be this on condition in outer join faster than where clause?

Question

Here is my table

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);

(created at DB Fiddle)

I want to find one person(anyone) that triggered an event name and retrieve all the records of that user on that day.

user_id	date_time	event_name	trivial	trivial_new
001	2020-12-10 10:00:00	a	1	13
001	2020-12-10 10:00:01	b	9	19
001	2020-12-10 10:00:02	c	3	21
001	2020-12-10 10:00:20	d	6	20
001	2020-12-10 10:00:40	e	2	11
002	2020-12-09 10:00:02	A	2	15
002	2020-12-10 10:00:09	B	4	15
002	2020-12-10 10:00:10	C	9	15
002	2020-12-10 10:00:50	D	0	13

Here is my code:

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 Substring_index(t_left.date_time, ' ', 1) =
                      Substring_index(t_right.date_time, ' ', 1)
ORDER  BY date_time

In the right table, there would be only one record that satisfies the conditions that the event_name is B, that is the user with the id 002. And then I join it with the left table on conditions that their user_ids are equal and the date is 2020-12-10, removing other users with different ids, 001, and the records of the same person whose events occurred not on 2020-12-10.

It works all well.

Then I modified my code to check if it would go as I expected to(it did, see here):

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  Substring_index(t_left.date_time, ' ', 1) =
       Substring_index(t_right.date_time, ' ', 1)
ORDER  BY date_time

In this case, I just join the tables by one condition and filter the dates of that user to get the right records.

I read some answers here and here and here where most of the examples are conditioning on a constant and some people say join would be faster while others state that the compiler will optimize the clauses and hence the speed would be the same.

I wonder if in my case the first one would be faster?

Any online platform to compare the speed?

Not suggesting that it will be faster but I prefer to use `DATE(t_left.date_time)` instead of `SUBSTRING_INDEX(t_left.date_time, ' ', 1)` to extract date. — FanoFN, Jan 30 '21 at 03:35
Those queries are the same function of input only if you could have used inner join rather than right join, ie if the right table never has unmatched rows. Your where is only true for rows that have non-null left table dates, so you could just use inner join. What is the point of right joining? If you need right join, your 2nd query is wrong--and performance is moot. PS Please: Research before asking & reflect research. Before asking about SQL performance, research it & research asking about it. PS Please put everything needed to ask in your post not just at a fiddle. — philipxy, Jan 30 '21 at 03:39
*Please rewrite to use `LEFT JOIN` instead of `RIGHT JOIN`.) — Rick James, Jan 30 '21 at 03:53
Why not compare performance 'offline'? Incidentally, note that nobody ever uses RIGHT JOIN. — Strawberry, Jan 30 '21 at 09:12
All discussion of performance is irrelevant because this is what's killing your performance: `AND Substring_index(t_left.date_time, ' ', 1) = Substring_index(t_right.date_time, ' ', 1)` — Strawberry, Jan 30 '21 at 09:16
Does this answer your question? [Right Join not returning all expected rows](https://stackoverflow.com/questions/18784519/right-join-not-returning-all-expected-rows) — philipxy, Aug 01 '22 at 07:05

score 1 · Answer 1 · answered Jan 30 '21 at 03:53

1

ON should be used to say how the tables are related.

WHERE should be used for filtering.

For plain JOIN (INNER JOIN), they are implemented identically.

For LEFT/RIGHT, they may make a big difference.

answered Jan 30 '21 at 03:53

Rick James

135,179
13
127
222

FanoFN · Answer 2 · 2021-02-01T08:24:55.263

If the second query is suppose to be the final query structure, you don't need to use RIGHT JOIN anymore, that's especially true since you're matching the dates in WHERE instead in ON.. effectively cancelling the right join (or left join). Actually, since you're doing right join to a sub-query that only extract a certain data for the table on the right, there's hardly any reason to right join, you'll got more differences in results if you do left join instead. Besides, In my experience, JOIN (INNER JOIN) is usually much faster than left/right join.

Another thing is, indexes make a lot of differences when it comes to performance. On your current example, there were no indexes so I recommend that you do that. I learned a lot about indexing from @Rick James' page here.

Here is a fiddle based on your example date consisting of your original attempt and few more:

RIGHT JOIN .. ON ... AND
RIGHT JOIN .. ON ... WHERE
LEFT JOIN .. ON ... AND
LEFT JOIN .. ON ... WHERE
adding index
EXPLAIN before and after index.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1b244deb6fad0dda0bf4aaf701e6bac2

PS: In the fiddle, I changed the date extraction to use DATE().

Will be this on condition in outer join faster than where clause?

2 Answers2