[WEBINAR] The New Normal: Dealing with the Reality of an Unsecure World

Query Optimizer

Definition - What does Query Optimizer mean?

A query optimizer is a critical database management system (DBMS) component that analyzes Structured Query Language (SQL) queries and determines efficient execution mechanisms. A query optimizer generates one or more query plans for each query, each of which may be a mechanism used to run a query. The most efficient query plan is selected and used to run the query.

Database users do not typically interact with a query optimizer, which works in the background.

Techopedia explains Query Optimizer

SQL queries may be simple or complex statements. Each SQL statement requires minimal use of valuable resources, such as disk reads and server memory. The query optimizer ensures this, as well as expedited execution of each SQL query. For example, a query optimizer may generate a series of query plans based on resource costs. One query plan may involve reading a table to retrieve a subset of its data, while another may involve using table indexes for quick data reading. These are known as cost-based optimizers.

A query optimizer may select different query plans for the same query, depending on environmental circumstances. For example, a user runs a query that selects approximately half of a table's data. The user runs the query when the server is heavily tasked with multiple simultaneous connections. In this scenario, the query optimizer may decide to use a query plan that calls on the created table indexes to satisfy the query, based on limited resources. This ensures minimal server drain by the query. By running the same query at another time with more resources, the query optimizer could determine that resource limitation is not an issue. In this instance, table indexes would not be used, and the query optimizer would allow full table loading to server memory.

Techopedia Deals

Connect with us

Techopedia on Linkedin
Techopedia on Linkedin
"Techopedia" on Twitter

Sign up for Techopedia's Free Newsletter!

Email Newsletter

Join thousands of others with our weekly newsletter

Free Whitepaper: The Path to Hybrid Cloud
Free Whitepaper: The Path to Hybrid Cloud:
The Path to Hybrid Cloud: Intelligent Bursting To Amazon Web Services & Microsoft Azure
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.