Tuning query and Query Optimization
Introduction Core Concepts Optimizer This is the general name for the entire component responsible for receiving SQL statements, analyzing them and deciding how the system will retrieve the data. Its task is to transform a declarative SQL statement (such as what data to get) into a specific physical execution plan (how to get that data step by step). This is the most general concept that most current databases like SQL or NoSQL use to automatically calculate the most efficient way to execute a query. Cost-based Optimizer This is a specific type of Optimizer that operates based on the principle of Cost calculation. It helps the Optimizer choose the most efficient execution path. To see the difference clearly, there are two main schools of Optimizer in computer science history: Rule-based Optimizer (RBO): Chooses the path based on fixed, rigid rules (For example: if an Index is available, it must be used, regardless of table size), which lacks flexibility and is now obsolete. Cost-based ...