CODEWITHSUNDEEP
phpmysqldatabase
user 007
user 007

Reputation: 779

Is there any way to check the performance of mysql Indexing

I have assigned Indexes for my tables. Is there any way to identify the performance of my query? Thank you

EDIT EXPLAIN result

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

Upvotes: 5

Views: 7168

Answers (2)

Omesh
Omesh

Reputation: 29121

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.

Upvotes: 6

Aijazs
Aijazs

Reputation: 426

Perhaps you can turn ON the slow-query-log, this might give additional information. Read this for more info: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

Upvotes: 1

Related Questions