Tim Tim - 1 year ago 67
MySQL Question

is any way to know name of indexing which mysql is using when I submit a query?

if I want to know if mysql is using the indexing I can use this SQL to check how many rows scanned for the SQL by checking the rows column returned:

EXPLAIN SELECT * FROM table_name1 WHERE `length` = 71;

but I wonder if anyway directly show me which indexing mysql is using for the progress. I think it will be more better system tell me the indexing name.

Take an example:

`id` int(3) unsigned NOT NULL AUTO_INCREMENT,
`num` int(11) NOT NULL,
`length` int(11) DEFAULT NULL,
KEY `length` (`length`) USING BTREE,
KEY `num_length` (`num`,`length`) USING BTREE

The query create a table named "tb1" and create an index for "length" and an index for "length" and "num";

I want to know if the 'length' index works properly.

EXPLAIN SELECT * FROM tb1 WHERE `length` = 71

;enter image description here

the result seems only show me 1666 rows scanned but didn;t tell me which index mysql used for the query.

what I want to know is how to get the index name which mysql is using when running a query.

Answer Source

The EXPLAIN says key = length. So that is probably the index being used. I say probably because the query might actually work differently than the EXPLAIN.

Something else is puzzling: AUTO_INCREMENT=2 implies that you have very few rows in the table. Yet Rows=1666 implies there are many rows. Which is correct?

If there are only two rows, it is unlikely to use any index; a table scan is likely to be faster.

In MariaDB 10.0 (and newer versions of MySQL?), there is a way for EXPLAINing a running query. However, this query probably runs too fast to let you do that. Also, the slowlog will have the explain -- use long_query_time=0.

As for knowing "when you submit the query", there is no way. You can only get a very likely answer by first doing EXPLAIN.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download