EXPLAIN your query in MySQL

EXPLAIN your query in MySQL

There are some easy ways available, using which we can check how SQL query is taking the index to its fullest extent. MySQL provide us a command called EXPLAIN, which can help us to explain our SQL query and give us some brief idea on how to write good SQL query.   For speeding up query, indexing is the most important factor. So, let us check out how index can be used for increasing performance. Also we need to taking care of some points where indexing degrade performance. When we fetch records from tables using SELECT statement sometimes it scans the whole table and increases the query execution time(degrade performance).   Example: Suppose we have a ‘products’, ‘users’ table in ‘companies’ database. we need a query for fetch all the products details belongs to specific user. SELECT p.name, p.price FROM products p WHERE p.user_id=2; This above query will give us expected result. But do you have any idea that this simple query scanned the whole ‘product’ table for fetch the result. We can check this by prefix EXPLAIN command in the above SELECT statement. 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 ALL NULL NULL    NULL NULL 45 Using where

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.