0

Assume the following tables, indexed on the obvious keys:

Table users
------------------------
id | name | has_cars
------------------------
1    Dan    0
2    Bob    0
3    Jake   1
4    Jen    1

Table cars
------------------------
id | user_id | description
------------------------
1    3         Corvette
2    3         Viper
2    3         Lamborghini
2    4         Camry

Is there any performance benefit to the latter of the following two queries?

SELECT users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON cars.user_id = users.id

vs

SELECT users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON users.has_cars > 0
AND cars.user_id = users.id

These tables, are of course for sake of example. I'm curious about the implications on much larger tables with very sparse has_cars > 0 condition-meeting-rows. Is the optimizer able to take advantage of using a row-local condition to determine whether or not to attempt the LEFT OUTER JOIN per row?

EDIT: Adding another potential use case to add legitimacy to the question.

Here's a potential use case: let's say you have a table with a type column. For type a rows you want to join the type_a table data, for type b rows you want to join the table_b table data, etc, and here a local condition would determine whether or not to perform a LEFT OUTER JOIN:

SELECT widgets.*, a_widgets.*, b_widgets.*, c_widgets.*
FROM widgets
LEFT OUTER JOIN a_widgets ON widgets.type = 'a' AND a_widgets.widget_id = widgets.id
LEFT OUTER JOIN b_widgets ON widgets.type = 'b' AND b_widgets.widget_id = widgets.id
LEFT OUTER JOIN c_widgets ON widgets.type = 'c' AND c_widgets.widget_id = widgets.id

For each table join, are you getting any performance benefit from the added local condition (widgets.type = _)? As compared to:

SELECT widgets.*, a_widgets.*, b_widgets.*, c_widgets.*
FROM widgets
LEFT OUTER JOIN a_widgets ON a_widgets.widget_id = widgets.id
LEFT OUTER JOIN b_widgets ON b_widgets.widget_id = widgets.id
LEFT OUTER JOIN c_widgets ON c_widgets.widget_id = widgets.id

(Assuming there is a widgets.type column, and if a row's type is a,b, or c there is a corresponding row in the a_widgets, b_widgets or c_widgets table.)

1

It is not 'proper' to have redundant information in a database. has_cars is redundant with EXISTS(SELECT 1 FROM cars WHERE user_id = ...).

So, once you eliminate has_cars, you are left with just a third option:

SELECT users.*, cars.*
    FROM users
    JOIN cars  ON cars.user_id = users.id

which is the preferred query.

Do not use LEFT unless you are hoping to 'find' rows missing from the right-hand table. In that case, use the LEFT JOIN ... IS NULL pattern:

SELECT users.name  'Does not have any cars'
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id
    WHERE cars.id IS NULL;          -- to list _only_ those without cars

Or to get data from cars whether or not there is a row:

SELECT users.name,
       cars.description  -- will be NULL if no cars
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id;

Another variant:

SELECT users.name,
       COUNT(cars.id)  AS 'how many cars owned (incl 0)'
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id
    GROUP BY users.id;

This is equivalent to that:

SELECT name,
       ( SELECT COUNT(cars.id) FROM cars  WHERE user_id = users.id
             )  AS 'how many cars owned (incl 0)'
    FROM users;

See also GROUP_CONCAT() as a way of listing (in a single cell) all the cars owned by each person.

  • Are indexes not redundant data? My example isn't a real-world example, but having a flag column could potentially yield large performance benefits on LEFT OUTER JOINs. That's really what I'm asking though - will a local flag such as the one in my example cause the optimizer to not attempt to join un-qualified rows? – Dan Jan 9 at 19:22
  • Adding a potential use case to the original question. – Dan Jan 9 at 19:28
  • @Dan - Sorry, that design (picking among multiple tables) is usually fraught with messiness, and should be avoided. – Rick James Jan 9 at 20:02
0

I did a test. Similar setup:

Table users
------------------------
id | name | has_cars
------------------------
1    Dan    0
2    Bob    0
3    Jake   1
4    Jen    1

Table cars
------------------------
id | owner_id | description
------------------------
1    3          Corvette
2    3          Viper
2    3          Lamborghini
2    4          Camry

users with 1,000,000 rows cars with 10,000 rows

EXPLAIN SELECT SQL_NO_CACHE users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON cars.user_id = users.id
LIMIT 1000

.004s

EXPLAIN 1

EXPLAIN SELECT SQL_NO_CACHE users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON cars.user_id = users.id
AND users.has_cars > 0
LIMIT 1000

.004s

EXPLAIN 2

Doesn't seem to make a significant performance difference.

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.