MySQL Performance: conditionnal left outer join vs where only

innodbMySQL

I have three tables:

  • FORUM ( id PRIMARY KEY, public BOOLEAN )
  • MESSAGE ( id PRIMARY KEY, content VARCHAR(255), user_id NULL FOREIGNKEY user.id, forum_id FOREIGNKEY forum.id)
  • USER( id PRIMARY KEY, username VARCHAR(25) )

So, basically, one message can belong at most to one user, or can be anonymous. On the other hands, one user can have multiples messages. Messages always belong to a forum, which can be "public" or not.

I have to create this query: return true if the specified forum is public, OR if the specified forum contains at least one message from a specified user.

SQL:

SELECT EXISTS (
    SELECT 1
    FROM `forum` AS `f`
    LEFT OUTER JOIN `message` AS `m` ON `m`.`forum_id` = `f`.`id`
    INNER JOIN `user` AS `u` ON `u`.`id` = `m`.`user_id`
    WHERE `f`.`id` = 10 AND (`f`.`public` = 1 OR `u`.`id` = 5)
    LIMIT 1 
) AS `boolean`

Such query works great. However my concerns is about performance. In my head, if the forum is not public, InnoDB has to do complete join between MESSAGE and USER, then filter the results with u.id = 5.

So If I wrote the query differently:

SELECT EXISTS (
    SELECT 1
    FROM `forum` AS `f`
    LEFT OUTER JOIN `message` AS `m` ON `m`.`forum_id` = `f`.`id`
    LEFT OUTER JOIN `user` AS `u` ON `u`.`id` = `m`.`user_id` AND `u`.`id` = 5
    WHERE `f`.`id` = 10 AND (`f`.`public` = 1 OR `u`.`id` IS NOT NULL) 
    LIMIT 1 
) AS `boolean`

The point of the second query is a new condition into the ON clause, to prevent InnoDB to do joins of the whole tables.

My question is: what's the fastest query ? Thanks!

Best Answer

If both tables are big enough so this is really a concern for you then fastest way is using two queries, one for each side of OR. That way you can be sure each query only accesses necessary rows, or even uses only index.

If you are worried about "sending" two queries, then you can "glue" them together using UNION - if you get at least one row, then one of those conditions was true. If result is empty, both were false.

Just a note: you can analyze your queries and see plan and estimated rows to check by using EXPLAIN.

Related Question