CSC675-775 Database Systems
Query Optimization
Jose Ortiz
jortizco@[Link]
Query Optimization
• More efficient query planning and optimization
SELECT * FROM books WHERE author = “Robert Martin”;
Query optimization
The lifecycle of a query
Query Optimization
The query parser
• Checks if the query is syntactically correct
• DBMS throws a SQL compile error otherwise
• Transform query into an algebraic expression
• Sends it to next step
Query Optimization
Query Planner and Optimizer
• Does the hard thinking work
• Performs straightforwards optimizations
• e.g 5 * 2 into 10
• Consider different query plans
• Different estimate costs
• Picks the optimal plan and passes it to the query
executor
Query Optimization
Query Executor
• Takes the plan from the optimizer
• Turns it into operations for the database
• Return results (if any)
Query optimization
• Two options:
• Full table scan
• Create an Index
• Heap, B+ Tree, Hash…
Query optimization
• How does the database system
decided which one to pick?
• Full scan or indexing?
Query optimization: Full
Scan
What is the cardinality of the column num_pages if table books have three records
and one of them is a duplicated one?
Full scan is performed when a table has low cardinality.
Time complexity for full scan? (not the same as cost analysis)
Query optimization: Indexing
Indexing structures (B+, Hashes) are created to optimize similar queries that
use the same key indexes in the condition (num_pages).
Normally done when full scan is so inefficient. Think about a table with one million books
Assuming that we run the above query and the optimizer decides to use indexing:
Time complexity to build the indexing structure when query runs for the first time?
What the second time the same query is executed?
What about if we change num_pages > 100 to num_pages > 200?
What about if we change num_pages > 200 to num_pages < 100 ?
What about if we change num_pages < 100 to book_price > 35.00?
Query Optimization
Where do humans come in?
•When dealing with big data or biggest data sets
• Query tuning
• SQL profiler
• Understanding your SQL engine
• In SQLite lite: EXPLAIN QUERY PLAN
• Manual optimization
• Plan ahead of time
Human Query Optimization
Best Practices
Human Query Optimization
• Define clear and good database requirements
• Identify relevant stakeholders.
• Focus on business outcomes.
• Frame the discussion for optimal requirements.
• Ask great questions.
• Write very specific requirements and confirm them with stakeholders.
13
Human Query Optimization
• When possible avoid “Select *…
• Inefficient
SELECT * FROM Employees;
• More efficient
SELECT name, address, city, zip_code FROM Employees;
14
Human Query Optimization
• Avoid DISTINCT
• Inefficient and inaccurate
SELECT DISTINCT name, lastName, state FROM Employees;
• Efficient
SELECT name, address, city, state, zip FROM Employees;
15
Human Query Optimization
• Create Joins with JOIN (not WHERE)
• Query with WHERE or also know as “cartesians joins”
• More query efficient with JOIN
16
Human Query Optimization
• One thing (or two) about GROUP BY:
• GROUP BY is one of the most used clauses in SQL.
• Keep in mind when using GROUP BY:
• GROUP BY X means put all those with the same value for X in the same
row.
• GROUP BY X, Y put all those with the same values for both X and Y in the
same row.
• Be careful with NULLS when using GROUP BY. Why?
17
Human Query Optimization
• Use WHERE instead of HAVING to define filters
• Let’s assume that we have a total of 1000 sales in the Sales table, did 200
sales in 2019, and we are interested in the number of sales per customer.
This query would pull 1,000 sales records from the Sales table, then filter for the 200 records
generated in the year 2019, and finally count the records in the dataset.
18
Human Query Optimization
• Use WHERE instead of HAVING to define filters
• A WHERE query limits the number of records pulled
This query would pull the 200 records from the year 2019, and then count the records in the dataset.
The first step in the HAVING clause has been completely eliminated. HAVING should only be used
when filtering on an aggregated field. In the query above, we could additionally filter for
customers with greater than 5 sales using a HAVING statement.
19
Human Query Optimization
• Use wildcards at the end of the condition (WHERE OR HAVING)
20
Human Query Optimization
• Use LIMIT to limit boundaries when possible. This will avoid unnecessary indexing. As may
know by now, indexing and scan operations in large tables are cost expensive.
21
Human Query Optimization
• Testing queries production databases is not recommended and should be
avoided at any cost.
• A better solution is to create testing instances of your database in your server
• If you have no other option than testing queries in production environments,
avoid running large queries >1,000,000 records during peak hours, try to do it
off-peak hours when possible
22