Geek Answers Handbook

Is there any way to check mysql indexing performance

I assigned indexes to my tables. Is there a way to determine the effectiveness of my request? thank you

EDIT EXPLAIN result

Here I am attaching the EXPLAIN test results to the test servers. There are only a few lines on the test server, but there are crowns of records on the real server, and it takes 10 to 15 minutes to complete the request. if it is not visible here I give the URL for this image

http://i45.tinypic.com/n6t8cx.jpg

+4
source share
2 answers

The following query will indicate whether the query uses an 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 a coverage index for better performance.

For the coverage index, you add columns that are used when clauses are first, and then columns used in the group by columns that are used in order, and then columns used in select.

eg. for the request above, you can add the coverage index KEY(col1, col2, col3)

* Note. Adding additional indexes will slow down your insertion requests.

+5
source

Perhaps you can enable a slow query log, this may provide additional information. Read this for more information: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

+1
source

More articles:


All Articles