MySQL explain results different after adding and then removing index

Question

Running into some odd behavior. I have two tables product and product_description with the following structures (some irrelevent columns omitted for brevity):

CREATE TABLE `oc_product` (
 `product_id` int(11) NOT NULL AUTO_INCREMENT,
 `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
 PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `oc_product_description` (
 `product_id` int(11) NOT NULL,
 `language_id` int(11) NOT NULL,
 `name` varchar(255) NOT NULL,
 `description` text NOT NULL,
 PRIMARY KEY (`product_id`,`language_id`),
 KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Since there are two possible values for language_id, each product has exactly two corresponding rows in the product_description table. Running the following query I was surprised by the value of product_description rows:

EXPLAIN
SELECT p.product_id
FROM oc_product p
 JOIN oc_product_description pd USING (product_id)
WHERE p.product_id = 12345;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | p | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | pd | NULL | ref | PRIMARY | PRIMARY | 4 | const | 60 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

Even though the primary key should be fine as is, out of curiosity I added a single index to product_description for only the product_id column:

ALTER TABLE `oc_product_description` ADD INDEX (`product_id`);

I try the explain query again and get different results, but oddly, the same keys are used:

+----+-------------+-------+------------+-------+--------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | p | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | pd | NULL | ref | PRIMARY,product_id | PRIMARY | 4 | const | 2 | 100.00 | Using index |
+----+-------------+-------+------------+-------+--------------------+---------+---------+-------+------+----------+-------------+

I then remove the additional index:

ALTER TABLE `oc_product_description` DROP INDEX `product_id`;

And run the same query again:

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | p | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | pd | NULL | ref | PRIMARY | PRIMARY | 4 | const | 2 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

Suddenly my row count has gone back to normal. Using a backup of the same database I was able to reproduce the same behavior. I tried both OPTIMIZE TABLE oc_product_description and REPAIR TABLE oc_product_description before but same 60 row count as before until I add (and then remove) the additional index on product_id.

Can anyone explain what on earth is going on here? I am using MySQL Server version 5.7.18-0ubuntu0.16.04.1-log (Ubuntu)

• Lennart - Slava Ukraini: I don't know enough about the internals of MySQL to answer your question, but I think it's worth noting that rows probably are an estimation of rows, not an exact number.
• You Old Fool: @Lennart - I believe you are correct, but I can't for the life of me figure out what changed the estimate so drastically in this case. I use EXPLAIN a lot and typically the estimate is very accurate
• Lennart - Slava Ukraini: I can only speculate in that MySQL for some reason uses statistics from the new index despite that it is using the PRIMARY KEY index. What happens if you add an index like (product_id, name) instead of (product_id)?

Answer 1

You should really switch to InnoDB. Virtually no effort has been put into MyISAM in several years.

I agree that "2" and "60" are drastically different. However, the rest of the EXPLAIN output implies that nothing changed based on that number.

Your query is quite artificial. It gets all translations for the one product, which seems not useful. It also fetches info that you already know -- product_id. Please switch to the actual query; there may be something interesting then.

• You Old Fool: Not much different in the actual query - I think only name and description columns were involved but the explain results were identical. thanks for the suggestion about innodb - i know it's off topic here but my primary resistance to that is that I'm working with an application that uses quite a few count(*) queries and was under the impression these can be very slow in innodb without taking a different approach. so switching to innodb would involve a lot of rewriting queries and functions. if you have any recommended reading on this topic I'd appreciate some links.
• Rick James: About the only case where InnoDB is slower is SELECT COUNT(*) FROM tbl -- without a WHERE clause.
• Rick James: And here are tips on the conversion: mysql.rjweb.org/doc.php/myisam2innodb
• You Old Fool: Is there a common workaround for that?
• Rick James: (1) avoid getting the exact count -- note that search engines no longer bother. (2) compute it daily. (3) Provide only an estimate. (4) Other ways that change the "user expectations".