CODEWITHSUNDEEP
mysqlsql
sanjihan
sanjihan

Reputation: 6014

Understanding output of EXPLAIN MYSQL command

I am running this command on a QOVENDOR table.

EXPLAIN SELECT * 
FROM QOVENDOR
WHERE V_NAME LIKE "B%"
ORDER BY V_AREACODE

QOVENDOR table:

CREATE TABLE `qovendor` (
  `V_CODE` int(11) NOT NULL,
  `V_NAME` varchar(35) NOT NULL,
  `V_CONTACT` varchar(15) NOT NULL,
  `V_AREACODE` char(3) NOT NULL,
  `V_PHONE` char(8) NOT NULL,
  `V_STATE` char(2) NOT NULL,
  `V_ORDER` char(1) NOT NULL,
  PRIMARY KEY (`V_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The output I get is:

+------+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+------+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | QOVENDOR | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using filesort |
+------+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+

This information should help me build more efficient queries, but I am having a hard time understanding how. The only non null column with some intersteing information is Extra, select_ype, Type and Rows. I am indeed using where clause, not sure what Using filesort means, besides that it relates to order by. How can I deduce if this query is the most efficient as it can be?

For evaluating performance I should have some sort of CPU Cost, and time data (like Oracle DBMS provides with EXPLAIN command).

Upvotes: 1

Views: 1867

Answers (1)

Shadow
Shadow

Reputation: 34231

In MySQL explain will not provide you with time, nor with CPU cost information. MySQL documentation on explain describes exactly the meaning of each column in the output:

Column JSON Name Meaning
id select_id The SELECT identifier
select_type None The SELECT type
table table_name The table for the output row
partitions partitions The matching partitions
type access_type The join type
possible_keys possible_keys The possible indexes to choose
key key The index actually chosen
key_len key_length The length of the chosen key
ref ref The columns compared to the index
rows rows Estimate of rows to be examined
filtered filtered Percentage of rows filtered by table condition
Extra None Additional information

From a query optimisation point of view, probably the type, possible_keys, key, rows, and extra fields are the most important. Their detailed description can be found on the linked MySQL documentation.

Upvotes: 1

Related Questions