03. How Query Plan Caching Works

-

 

How Query Plan Caching Works


When a SQL query is executed:

First Time Execution:

  • The database parses and optimizes the query.
  • It saves the execution plan in memory (query cache).

Next Time (Same Query):

  • If the exact same query (including whitespace and case) is executed again,
  • The database reuses the saved plan.
  • This saves CPU usage and makes the query faster

 

MySQL:

  • MySQL does not cache execution plans for regular SQL queries (unlike Oracle or SQL Server).
  • It only caches plans for Prepared Statements and Stored Procedures.

MongoDB:

  • MongoDB is handle a cache for the all the queries (regular queries, But not it happen in MySQL).

 

What happens when the  underlying database structure (like indexes) changes after a query plan has been cached

When a database index changes (created, dropped, or modified), the cached execution plan might become invalid, suboptimal, or even fail. So the database needs to detect the change and take action.

1. MySQL:

MySQL automatically invalidates cached plans for affected tables. On next execution, the plan will be recompiled with the new index metadata.

MySQL checks a "version" or metadata change of the table/index to determine plan invalidation.

2. MongoDB

Do a trial run again on the next query execution. Cache the new best plan based on the updated index situation.

MongoDB also evicts stale plans over time via LRU (Least Recently Used).

 

DBMS

Plan Selection Method

Plan Caching (Raw Queries)

Reuse with Prepared Statements

Notes

Oracle

Cost-Based Optimization (CBO)

Yes (Library Cache)

Yes

Very advanced optimizer; uses Adaptive Plans

MS SQL Server

Cost-Based Optimization

Yes (Plan Cache)

Yes

Includes parameter sniffing, plan freezing

PostgreSQL

Cost-Based (no plan cache for raw SQL)

No (re-optimizes each raw SQL)

Yes

Optimizes each query unless using prepared

MySQL

Cost-Based

No (unless using prepared stmt)

Yes

Plan cache deprecated in 8.x for raw SQL

MongoDB

Trial Execution (real data)

Yes (Plan Cache after 1st run)

N/A (no prepared stmt needed)

Plan cache auto-managed

 

Comments

Post a Comment

Popular posts from this blog

Database - Topics

-
Image
 Topics  01. Steps in SQL Query Execution 02. How does the database create a query plan 03. How Query Plan Caching Works 04. What is the need for an execution plan? 05. How Do Query/ Query Plan Execute (Inside MySQL Server) 06. Importance Of Undo Log 07. How Row Lock Is Handle When Query On The Table Row 08. Technique to recover the committed changes 09. Auditing technique 10. Query cache 11. DB connection pool 12. What is ACID? 13. Optimizing SQL queries
Read more

02. Spring – Creating spring project clone it with GIT step by step.

-
Image
  Creating spring project clone it with GIT step by step. 01. Create an empty GIT repository and select a repository name. 02.   Check to add README.md and technology to add it inside the gitignire file0- it will include all the generated class when we complete the application. 03.      Copy the github repository URL. 04 . Open eclipse and go to,             Window – Show View – Other - Type git on the folder and select git repositories. 05.   Now clone this repository at your desired location in which we are going to keep the project specific file . a)         a)  Step One.             b)  Step  Two .          c)    Step Three. Both projects are generated as bellows           d) Step Four  Open the gitingnore file and ignore the target folder of the project. All the class files which are ...
Read more

01. Steps in SQL Query Execution (MySQL)

-
Image
Steps in SQL Query Execution (MySQL) 1. Client Sends Query to Server The SQL statement is sent to the MySQL server over a connection (e.g., via JDBC or a query tool like MySQL Workbench). 2. Parser / Syntax Check The parser checks the SQL syntax.If there are any syntax errors, it throws an error immediately. 3. Pre-processor Verifies privileges and permissions for the user executing the query. Resolves object names (like table or column aliases). 4. Query Optimization The optimizer evaluates multiple strategies to execute the query. It considers: Which indexes to use. Join order (for multi-table queries). Whether to use temporary tables. Cost of different execution paths. The optimizer chooses select the most efficient query plan (Cost effective -> memory, CPU etc). 5. Query Execution Plan (Most cost effective query plan) The query plan is a blueprint of how MySQL will fetch the data. You can view this with the EXPLAIN keyword in front of your query. 6. Query Execution The storage e...
Read more