EXPLANATION:
id: This is a sequential number of the SELECT within the query.
select_type: type of SELECT. Currently it shows SIMPLE because it is using single table without any UNION or subquery.
table: Table from which row of output refer.
type: ‘ALL’ , This will indicate full table scan for each combination of rows, which is not good. Using index we can avoid full table scan.
possible_keys: this will listed out possible indexes. NULL, means no index is available.
key: key column, which is going to be used as index for current query.
key_len: Indicates length of the index key. NULL, means no key value present.
ref: Which columns or constants are used with the key to select rows.
rows: Number of rows that MySQL believes it must examine to execute the query.
extra: Additional information about how MySQL will resolve the query.
For avoid full table scan in the above mentioned query, we need to index ‘user_id’ column(used in WHERE condition) of ‘products’ table.
CREATE INDEX
USER_ID
ON products(user_id);
This above statement will add an index named ‘USER_ID’ against ‘user_id’ field of ‘products’ table.
After this lets execute our previous SELECT query again to check whether full table scan issue is still exist or not.
EXPLAIN
SELECT
p.name,
p.price
FROM products p
WHERE p.user_id=2;
RESULT:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | p | ref | USER_ID | USER_ID | 4 | const | 22 | (null) |
Now, check the difference. Here we can see that ‘rows’ column value is half of its previous value(45) and ‘type’ is not ‘ALL’.
Also it currently using the ‘USER_ID’ index.
For more detail on indexing you can refer:
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
DEMERITS:
If we will going to index everything then that will also create performance issue because every index takes extra disk space and degrade performance. It will slow down the write operation(insert and delete) as add/delete a record to the table not only going to add/delete row value, it require changes in the indexing value as well. So, a table having more index need more changes and degrade performance accordingly. So, we need to keep this in mind also.