CODEWITHSUNDEEP
mysqlquery-performance
Liki Crus
Liki Crus

Reputation: 2062

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?

Upvotes: 0

Views: 312

Answers (2)

Rick James
Rick James

Reputation: 142278

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.

Upvotes: 1

ysth
ysth

Reputation: 98388

Which one performs better for you? Without seeing your code, no one will be able to tell you. If your function is just doing that exact same query, I wouldn't think the function would be any faster, unless the subquery is somehow causing your entire query to be misoptimized. Try it and see is the only real answer. There isn't any caching of function results that would make them any faster; even built in functions aren't cached, see https://stackoverflow.com/a/33060896.

Upvotes: 1

Related Questions