2

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!

  • What version of MySQL? Have you checked the execution plans of each to see for yourself? – LowlyDBA Jun 16 '15 at 18:55
  • LIMIT 1 is probably obviated by EXISTS. – Rick James Jun 17 '15 at 0:52
2

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.

|improve this answer|||||
  • Thank you so much. You're totally right. I have checked lot of execution plan and it appears if public=1 (first side of the OR), MySQL Engine doesn't return immediately and has to access all rows which is very bad for performance. – Tutux Jun 16 '15 at 20:46

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.