Explain Plan
==========
- Prefix a SELECT query with EXPLAIN. MySQL won't actually execute the query, it only analyses it.
- In 3 ways, we can optimise the query:
: Modify or create indexes
: Modify query structure
: Modify data structure
- Keep indexes in memory by trimming the fat:
: Reduce the characters in VARCHAR index
: Use TINYINT instead of BIGINT
- MySQL will only use 1 index per query/table. It cannot combine 2 separate indexes to make a useful one.
Case-1:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: recipes
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
table: The table entry tells you which table this relates to - not at all useful in this example but when you run a select statement with multiple tables through explain, you'll get one row per table so this column tells you which table it is.
possible_keys: The possible_keys shows which indexes apply to this query and the key tells us which of those was actually used - here there are no keys that apply.
rows: Tell us how many rows MySQL had to look at to find the result set.
Note: The ideal outcome is that the rows number is the same as the number of results from the query
Case-2: In explain plan of a query, getting: Using where; Using temporary; Using filesort
- Using temporary means that MySQL need to use some temporary tables for storing intermediate data calculated when executing your query.
- Using filesort is a sorting algorithm where MySQL isn't able to use an index for sorting and therefore can't do the complete sort in memory. Instead it breaks the sort into smaller chunks and then merge the results to get the final sorted data.
Case-3:
possible_keys: All the possible indexes which MySQL could have used. Based on a series of quick lookups and calculations.
- FTS are almost always the slowest query.
References:
http://www.slideshare.net/phpcodemonkey/mysql-explain-explained
==========
- Prefix a SELECT query with EXPLAIN. MySQL won't actually execute the query, it only analyses it.
- In 3 ways, we can optimise the query:
: Modify or create indexes
: Modify query structure
: Modify data structure
- Keep indexes in memory by trimming the fat:
: Reduce the characters in VARCHAR index
: Use TINYINT instead of BIGINT
- MySQL will only use 1 index per query/table. It cannot combine 2 separate indexes to make a useful one.
Case-1:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: recipes
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
table: The table entry tells you which table this relates to - not at all useful in this example but when you run a select statement with multiple tables through explain, you'll get one row per table so this column tells you which table it is.
possible_keys: The possible_keys shows which indexes apply to this query and the key tells us which of those was actually used - here there are no keys that apply.
rows: Tell us how many rows MySQL had to look at to find the result set.
Note: The ideal outcome is that the rows number is the same as the number of results from the query
Case-2: In explain plan of a query, getting: Using where; Using temporary; Using filesort
- Using temporary means that MySQL need to use some temporary tables for storing intermediate data calculated when executing your query.
- Using filesort is a sorting algorithm where MySQL isn't able to use an index for sorting and therefore can't do the complete sort in memory. Instead it breaks the sort into smaller chunks and then merge the results to get the final sorted data.
Case-3:
possible_keys: All the possible indexes which MySQL could have used. Based on a series of quick lookups and calculations.
- FTS are almost always the slowest query.
References:
http://www.slideshare.net/phpcodemonkey/mysql-explain-explained
No comments:
Post a Comment