Is there any way to check the performance of mysql Indexing

database, mysql, php

Solution

Following query will tell you whether query uses index or not:

EXPLAIN EXTENDED SELECT col1, col2, col3, COUNT(1) 
FROM table_name 
WHERE col1 = val 
GROUP BY col1 
ORDER BY col2;

SHOW WARNINGS;

You can add covering index for best performance.

For covering index you add columns used in where clauses first then columns used in group by the columns used in order by and then columns used in select.

e.g. for above query you can add covering index `KEY(col1, col2, col3)`

*Note Adding more indexes will slow down your insert queries.

Problem

I have assigned Indexes for my tables. Is there any way to identify the performance of my query? Thank you EDIT Here i am attaching my test servers EXPLAIN result. In test server there are only few rows but in live server there are crores of records and it takes 10 to 15 minutes to execute query. if its not visible here i am giving url for this image http://i45.tinypic.com/n6t8cx.jpg

Original source