Weird Index activity in mysql Join

Question

I have a countries table structured this way with an index in the iso2 column:

---

---

When executing a normal select query, the iso2 index works fine:

---

---

But when connecting to another table on iso2, this behaves very strangely:

---

---

First of all, he says NULL possible_keys, but then he still uses it? Then he also says 256 rows, which are the whole table. It works very slowly with large updates, so I can say that it does not use an index. What is the problem?

EDIT: Also, if I post the id column from the iso2 index (see the first image), then it will say that no index is used in the connection.

---

Additional Information: I am trying to normalize my data and use country_id instead of country . I was updating tables using country_id when I noticed that this was very slow. Several EXPLAIN made me find out that the index was not used. Maybe this has something to do with iso2 being char (2)?

I populated country_id as follows:

 UPDATE leads LEFT JOIN countries on leads.country=countries.iso2 SET leads.country_id=countries.id 

This query took almost 40 seconds for about 100 thousand rows in the leads table.

Answer 1

What EXPLAIN means is that it performs a full index scan, which is not ideal, but not necessarily bad; in this case, this is normal. The iso2 index is a covering index , which means that it contains all the columns that are needed from this table for this query. Basically, the query optimizer decided that instead of searching for the row that is needed from the index and then reading this row from the table to get the id , it will simply go to the next part of the index, since it is faster.

I would expect the optimizer to use a link to the index, but apparently he decided it was not profitable. Since the index is small, I would not worry about this without using the link to the index.

A few other points:

• Could you talk about the fact that "very slow updates on large updates"?
• Is the table used with MyISAM or InnoDB?
• countries.id could probably be changed to SMALLINT UNSIGNED ; which is almost the same as INT(5) , but uses 2 bytes instead of 4.
• CHAR(2) - best data type for iso2