Please enable JavaScript.
Coggle requires JavaScript to display documents.
Query Optimization - Coggle Diagram
Query Optimization
Optimizing Specific Types of Queries
Optimizing COUNT() Queries
COUNT() is a special function that works in two very different ways: it counts values and rows.
Value is a non-NULL expression.
COUNT() counts how many times that expression has a value.
COUNT() simply counts the number of rows in the result.
When you want to know the number of rows in the result, you should always use COUNT(*).
should be used if u want to count all but small number of rows
Optimizing JOIN Queries
any GROUP BY or ORDER BY expression refers only to columns from a single table, so MySQL can try to use an index for that operation.
Optimizing Subqueries
You should usually prefer a join where possible! :check:
Optimizing GROUP BY and DISTINCT
MySQL has two kinds of GROUP BY strategies when it can’t use an index:
it can use a temporary table
a filesort to perform the grouping
If you need to group a join by a value that comes from a lookup table, it’s usually more efficient to group by the lookup table’s identifier than by the value.
Optimizing LIMIT and OFFSET
Queries with LIMITs and OFFSETs are common in systems that do pagination
nearly always in conjunction with an ORDER BY clause
Problem
having a high value for the offset
To optimize queryes, you can either limit how many pages are permitted in a pagination view, or try to make the high offsets more efficient.
Optimizing UNION
MySQL always executes UNION queries by creating a temporary table and filling it with the UNION results.
MySQL can’t apply as many optimizations to UNION queries as you might be used to.
Help way is "pushing down" WHERE, LIMIT, ORDER BY
It’s important to always use UNION ALL, unless you need the server to eliminate duplicate rows.
MySQL always places results into a temporary table and then reads them out again
Slow Query Basics
To optimize a query, you must optimize its subtasks by
eliminating them
making them happen fewer times
making them happen more quickly
Optimize Data Access
most bad queries can be changed to access less data.
Poorly performing query
Find out whether your app is retrieving mpre data than u need
Find out whether the MySQL server is analyzing more rows than it needs.
Asking the Database for Data You Don't Need
Typical mistakes
Fetching more rows than needed
Fetching all columns from a multitable join
Fetching all columns
Fetching the same data repeatedly
MySQL Examing Too Much Data
response time
simplest query cost metrics
is the sum of two things
service time
how long it takes the server to actually process the query
queue time
is the portion of response time during which the server isn`t really executing the query
MySQL can use several access methods to find and return a row.
Query Execution Basics
Process MySQL follows to execute queries
The client sends the SQL statement to the server
The server checks the query cache
The server parses, preprocesses, and optimizes the SQL into a query execution plan
The query execution engine executes the plan by making calls to the storage engine API
The server sends the result to the client
Query Optimization Using EXPLAIN
Explain statement is used to obtain a query execution plan
EXPLAIN [explain_type] {explainable_stmt | FOR CONNECTION connection_id} explain_type: {EXTENDED | PARTITIONS | FORMAT = format_name} explainable_stmt: {SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement}
Explain works with
SELECT
DELETE
INSERT
REPLACE
UPDATE
Explain helps to see where u should add indexes to tables so that the statement executes faster by using indexes to find rows
Explain is also used to check whether the optimizer joins the tables in an optimal order
Explain returns a row of information for each table used in the SELECT statement
MySQL can use indexes on columns more efficiently if they are declared as the same type and size.