CODEWITHSUNDEEP
mysqlsqldatabasepostgresql
mux
mux

Reputation: 475

Does MySQL/PostgreSQL cache the parsing/compiling of queries?

Suppose I execute a query in MySQL (or PostgreSQL), let's say: 

SELECT * FROM USER WHERE age = 20;

Does the database engine parse and compile the query/statement each time I execute it? Or does it hold some cache of the previous statements/queries?

If it has a cache mechanism, does it treat the following two queries differently?

/* first query */
SELECT * FROM USER WHERE age = 20 AND name = 'foo';

/* second query */
SELECT * FROM USER WHERE name = 'foo' AND age = 20;

I'm asking that because I'm using some tool for generating the SQL queries in my code, that doesn't consistent with the order of the conditions in the queries. I just want to be sure that this behavior doesn't effect my database performance.

Thanks

Upvotes: 1

Views: 609

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270783

SQL is a declarative language, not a procedural language. What actually gets executed is basically a DAG -- directed acyclic graph -- of components that you probably would not recognize as SQL constructs (such as "hash join" or "filter" or "sort").

The two queries you mention with conditions on name and age are going to compile to essentially the same compiled form. If you have appropriate indexes or partitions, both queries will use them. If you don't, then they might execute the boolean conditions in different orders. However, the big overhead on such a query is the full table scan, not the individual comparisons.

Under some rare circumstances, you might want to be sure that conditions are executed in a particular order -- especially if you have an expensive user-defined function. The compiler will often do this for you. If not, you can use a case expression:

where (case when col <> 0 then 0
            when expensive_function( . . . ) then 1
            else 0
       end) = 1

This will execute the expensive function only when col = 0, because case expressions evaluate their expressions in sequential order (in a non-aggregation query).

As for caching, that depends on the server and options. In general, databases cache query plans, so they don't need to be recompiled. And this is often at the prepared statement level rather than the text of the query. Databases don't generally cache results, because the data in the underlying tables might change.

Upvotes: 2

Related Questions