CODEWITHSUNDEEP
mysqlexplain
wanna
wanna

Reputation: 79

Why EXPLAIN SQL result for KEY is NULL

I have created INDEX for my table but when use explain QUERY the result for key is NULL.

my table as below:

TABLE list_country

INDEX FOR list_country:

TABLE ref_country

INDEX for ref_country:

i run explain query as below:

EXPLAIN 
SELECT ctr.id_tx
, GROUP_CONCAT(rctr.country_name,':',cost) AS cost_country
, GROUP_CONCAT(rctr.country_name,':',cceiling) AS ceiling_country
, GROUP_CONCAT(rctr.country_name) AS country 
FROM list_country ctr
LEFT JOIN ref_country rctr ON rctr.id = ctr.id_ref_country 
GROUP BY id_tx

RESULT EXPLAIN FOR TABLE list_country TYPE = ALL, KEY = NULL

Why the key is null for list_country even i specify the index?

The DDL for this table:

CREATE TABLE `list_country` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_tx` INT NOT NULL,
  `id_ref_country` INT NOT NULL,
  `cost` DECIMAL(15,2) DEFAULT NULL,
  `cceiling` DECIMAL(15,2) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `country_unik` (`id_tx`,`id_ref_country`) USING BTREE,
  KEY `id_tx` (`id_tx`) USING BTREE,
  KEY `id_ref_country` (`id_ref_country`) USING BTREE,
  CONSTRAINT `list_country_ibfk_1` FOREIGN KEY (`id_tx`) REFERENCES `ep_tx` (`id_tx`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `list_country_ibfk_2` FOREIGN KEY (`id_ref_country`) REFERENCES `ref_country` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=INNODB AUTO_INCREMENT=55609 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Upvotes: 0

Views: 445

Answers (1)

Luuk
Luuk

Reputation: 14929

To get the results for you query, MySSQL needs to get the info of the following fields:

  • ctr.id_tx
  • ctr.id_ref_country

Because of this, only the index country_unik can be used, it contains both fields, or MySQL can just read the complete table.

EXPLAIN Output Format says, about Type=ALL:

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

MySQL is avoiding the use of the index, because it needs all records for that table.

Upvotes: 1

Related Questions