Search code examples
mysqlquery-performance

MySQL Performance - Sub Query vs Custom Function

I have been using the same subquery to check the specific functionality in many SQLs. e.g. a complex user permission checking.

In this case, which one is better among using that subquery and using the custom function in performance?

The subquery is the following:

(SELECT 1 FROM `perm_categories_map` WHERE whatever={parentTable.whatever} AND category_id={Constant})

and its usage example is

... AND EXISTS (SELECT 1 FROM `perm_categories_map` WHERE whatever={parentTable.whatever} AND category_id={Constant}) ...

I am going to make a function called check_perm so that I can call like ... AND check_perm({parentTable.whatever}, {constant}) ...

But before doing it, I'd like to confirm that function is faster than subquery from a performance perspective.

I would think function is faster than the subquery. What's your opinion?

Solution 
  • EXISTS(SELECT 1 ... )

    is a special construct called a "semi-join". It says "perform the following SELECT but stop at soon as you find a match."

    Any other use of that same "subquery" ((SELECT 1 ... )) will run to completion, potentially producing multiple rows, each with 1 in it.

    So, EXISTS is faster, at least in some situations.

    Think of EXISTS as a boolean function returning TRUE/FALSE (or, equivalently, 1/0). Also the 1 in side the SELECT can be anything.

    MariaDB has a subquery cache; MySQL does not.

    There may be some caching of the "parsing" in stored routines (see PREPARE and its friends), but not in stand-alone queries.