01. Steps in SQL Query Execution (MySQL)

-

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 engine (like InnoDB) executes the plan:  
                  Reads from indexes or full table scans. Performs joins, filters, sorts, etc.

7. Result Sent to Client

  • The result set is sent back to the client in chunks.

Comments

Post a Comment

Popular posts from this blog

09.Data Binding.

-
Image
Data Binding. Data binding ආකාර 2 කි. One-way Data Binding - component to view (DOM) or view to the component Two-way Data Binding - component to view and view to the component. One-way Data Binding in Angular Component to view (DOM) or view to component One-way data binding will bind the data from the component to the view (DOM) or from view to the component. One-way data binding is unidirectional. You can only bind the data from component to the view or from view to the component. a). From Component to View      01.   Interpolation Binding      02.   Property Binding      03.   Attribute Binding      04.   Class Binding      05.   Style Binding b). From View to Component      01.  Event binding  From Component to View     Style and Class Binding Directives – Directives are nothing but custom html element that angular provi...
Read more

Data Normalization

-
Image
  DATA NORMALIZATION This is a technique of splitting a complex table in to simple meaningful tables. In here arrange tables, rows and relationships between tables in order to avoid data redundancy ( අතිරික්තතාව ) and to attain data integrity ( සම්පූර්ණත්වය ).Goal, 01.    To avid data redundancy (data duplicated). 02.    To attain data integrity - Data goes to inconsistence state ( ස්ථිර නෑ , අදාළ නෑ ) . 03.    To put data in to correct table. 04.    To avoid CRUD anomalies ( අක්‍රමිකතාව ). Duplicate වෙන data නවත්වන්න ඕනේ , අදාළ නැති data store වීම නවත්වන්න   ඕනේ.නියමිත table වලට පමණක් නියමිට data යවන්න ඕනේ සහ DB එක සමග කරන CRUD operation වලදී අක්‍රමිකතා ඇති වෙන්න බෑ (අනවශ්‍ය data delete වීම , අනවශ්‍ය data add වීම ,data search කරගන්න බැරිවීම වගේ ඒවා) Partial dependency It is the dependency where non key attributes are functionally depends on any part of composite key. Full dependency It is the dependency where non ke...
Read more

Why you see it in IntelliJ but not in your project folder

-
Image
  Why you see it in IntelliJ but not in your project folder IntelliJ shows all dependencies under External Libraries, but they are not stored directly inside your project directory. Instead, they are downloaded and cached by your build tool (like Gradle or Maven) in a global location on your system. ~ /.gradle/ caches/modules- 2 /files- 2.1 / ~ / .m2 / repository/ You have a Maven project that uses a:1.2 → it downloads the JAR to ~/.m2/repository. Now, in a Gradle project, you also want to use a:1.2 What actually happens: Gradle does not use Maven’s .m2/repository directly. Instead: Gradle will check its own cache: ~/.gradle/caches/modules-2/files-2.1/ If a:1.2 is not found in Gradle's cache, it will: Download a:1.2 from the remote repository (e.g., Maven Central) again. Store it in the Gradle cache, not reuse from .m2. Why this happens: Gradle and Maven use different caching strategies and metadata formats, so they maintain separate repositories. BUT: You can configure Gradle to l...
Read more