MySQL query optimization Flashcards
What is true about the query optimizer?
a) usually generates multiple query plans
b) always generate one single query plan
c) the developer can choose which query plan to execute
d) all options are wrong
a)
Which of the following fields have the highest index cardinality?
a) first_name
b) address
c) number_of_children
d) email
d)
Assuming that there is no index by column ID on table products and the following query takes 3 seconds:
SELECT * FROM products WHERE id = 2;
a) the execution time is normal
b) the query can be considered “slow”
c) we cannot improve the execution time
d) adding an index by id will reduce the execution time at most 50%
b)
What is a query execution plan?
It is a list of instructions that the database needs to follow in order to execute a query on the data. Is generated to know if a query is slow or not
The user can determine what is the plan that is executed. True or false?
False. The query optimizer generates multiple query plans for a single query and determines the most efficient plan to run.
What is the explain command?
To examine the query plan we use the EXPLAIN command, which shows the query plan. This command does not run the actual SQL statement (except subqueries), and does not provide any tuning recommendations, but it does provide valuable information to help us make tuning decisions!
What does the key column means in the following code?
EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176;
id: 1
select_type: SIMPLE
table: inventory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 787338
Extra: Using where
The query is not using an index. So it can be considered a poorly tuned SQL query.
How to identify primary issues using the explain command?
- No index used (NULL specified in the key column).
- A large number of rows processed (the rows column).
- A large number of indexes evaluated (the possible_keys column).
If we add an index, the query execution plan will change. True or false
False. The generated query plan is not guaranteed and can change, depending on several factors.
For update and delete statements, we need to rewrite the query as a select statement to confirm index usage. True or false
True
What are indexes?
An index is a structure that is sorting and a pointer from each record to their corresponding records in the original table where the data is stored. Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries. Inde xing makes columns faster to query by creating pointers to where data is stored within a database.
Indexes are stored and searched as B-trees. True or false
True. It creates a tree-like structure that sorts data for quick searching.
How to create an index after table creation?
CREATE INDEX index_name ON table_name (column_list);
This can take a lot of time.
When we have multiple different indexes, the user can choose what is the best indexing for the query. True or false
False. when we have multiple different indexes (that are listed in possible_keys of the query plan, explain command), MySQL tries to identify the most effective index for the query. The optimizer chooses an index based on the estimated cost to do the least amount of work, not what a human considers the right order
Which datatype to use when we want to store exact monetary values?
decimal
