How to check sql query performance ?

Are you getting performance issue in query ?
it would be help full for improvement of query performance.now i am going to start how to check query performance, its very simple.
Mysql did introduce “EXPLAIN” keyword.
Fetch the records from database using SELECT command. its not necessary always perform speedy sometime it scan whole table that will increase query execution time.
suppose that:
Select tab.name, tab.description, tab.category_no from cateory as tab from tab.user_id =2;
Definitely it will give expected result. but do you know it will scan full table.
we can test it prefix EXPALIN command in the query.like that:

EXPLAIN QUERY

EXPLAIN SELECT * FROM TABLENAME;

i want to check same thing in the above query:

EXPLAIN Select tab.name, tab.description, tab.category_no from category as tab from tab.user_id =9;  

Result

id :	1
select_type:  SIMPLE
table tab:
type: ALL
possible_keys:  NULL
key:  NULL 
key_len:  NULL
ref: NULL
rows: 89
Extra: using where

EXPLAIN is available also for INSERT, UPDATE and DELETE

• 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.

CREATE INDEX USER_CAT  ON category(user_id);
After applying index. now checking above query performance
EXPLAIN Select tab.name, tab.description, tab.category_no 
from category as tab from tab.user_id =9;   

Result

id : 1
select_type: Simple
table : tab
type: ref
possible_keys: USER_CAT
key: USER_CAT
key_len: 3
ref: const 
rows : 12
Extra: null

first query is scanning 89 rows and last query scanning only 12 rows.
add another keyword for additional information about teh way it executes
EXTENDED after EXPLAIN in the query.

 
EXPLAIN EXTENDED SELECT * FORM TABLE_NAME;  

Feel the difference