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

Question

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

maybe You shold read about prepared statements (depends on language / driver You are using) — Jacek Cz
– Jacek Cz, Commented May 12, 2018 at 14:20
If you are using an ORM framework like Hibernate to call the database, then your question changes a bit. Most ORM frameworks certainly cache entities (i.e. records). So if you made the first query, the entity would be cached, and the second query wouldn't even need to hit the database. — Tim Biegeleisen
– Tim Biegeleisen, Commented May 12, 2018 at 14:24

Gordon Linoff · Accepted Answer · 2018-05-12 15:04:44Z

2

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.

edited May 12, 2018 at 15:04

answered May 12, 2018 at 14:26

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mux Over a year ago

You mentioned that "databases cache query plans", what is the "key" for the cache? the text representation of the query? I wonder if I should care that the two queries are not look the same, in terms of performance.

Gordon Linoff Over a year ago

@AfekDror . . . Good question. It depends on the database. I think some will use the "text" form of the query. MySQL and Postgres cache at the prepared statement level.

mux Over a year ago

Thanks for your help Gordon.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related