Please enable JavaScript.
Coggle requires JavaScript to display documents.
QUERY OPTIMIZATION - Coggle Diagram
QUERY OPTIMIZATION
EXPLAIN Output Columns
possible_keys
the indexes
key
the key that MySQL actually decided to use
type
the join type
key_len
the length of the key
partitions
the partitions from which records would be matched
ref
which columns or constants are compared to the index
table
the name of the table
rows
the number of rows
select_type
the type of SELECT
filtered
an estimated percentage of table rows
id
the sequential number of the SELECT
extra
additional information
Main considerations for optimizing
make a SELECT ... WHERE query faster
isolate and tune any part of the query
minimize the number of full table scans
keep table statistics up to date
adjust the size of the memory areas
deal with locking issues
Query cost metrics
Number of rows
examined
Number of rows
returned
Response Time
Service Time
is 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
EXPLAIN statement
obtains a query execution plan
Works with
DELETE
INSERT
REPLACE
SELECT
UPDATE
Explains
how tables are joined
estimated transaction cost
how the operator is processed
what indexes should be created