2

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)

3
  • 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.
    – Lennart
    Aug 2, 2017 at 21:12
  • @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 Aug 2, 2017 at 21:14
  • 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)?
    – Lennart
    Aug 2, 2017 at 22:49

1 Answer 1

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.

5
  • 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. Aug 13, 2017 at 21:26
  • 1
    About the only case where InnoDB is slower is SELECT COUNT(*) FROM tbl -- without a WHERE clause.
    – Rick James
    Aug 13, 2017 at 21:50
  • 1
    And here are tips on the conversion: mysql.rjweb.org/doc.php/myisam2innodb
    – Rick James
    Aug 13, 2017 at 21:51
  • Is there a common workaround for that? Aug 13, 2017 at 21:51
  • (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".
    – Rick James
    Aug 13, 2017 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.