Two tables are given:

CREATE TABLE `cities` ( `city_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `enabled` ENUM('0','1') NULL DEFAULT NULL, `domain` VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (`city_id`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM; CREATE TABLE `cities_loc` ( `city_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NULL DEFAULT NULL, `name_ro` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`city_id`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM;

There are data in the tables. I make such a request:

 SELECT * FROM cities c JOIN cities_loc cl ON c.city_id = cl.city_id

EXPLAIN this query shows that the index does not apply, in POSSIBLE KEYS PRIMARY , and in fact KEY is empty, and rows = 327 .

enter image description here

Why don't indexes work in this case?

    2 answers 2

    To perform this query, you will need to perform a full table scan, matching each record to another record from the "other" table. Therefore, the optimizer decided not to use the index. Especially since you extract all the data.

    • Yes, thank you, I already understood everything, all because of my neglect (I still had a condition, one record was selected on it, and this field did not have an index) - Alexey Shishkin

    Well, why, apply.

    You still want everything from c , so there is no sense in searching the index: there are no criteria. They asked for all 327 lines - get and sign .

    But the nested search, from JOIN 'a, uses the primary key cl , so that for each entry from c find the corresponding entry in c.city_id , one. For this search, the index already makes sense and is used.