2

I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used.

Schema and sample data:

CREATE TABLE `px_po_status` (
  `po_status_id` tinyint NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`po_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `px_po_status` (`po_status_id`, `name`)
VALUES
  (0, 'PENDING'),(1, 'SENT'),(3, 'COMPLETE');

CREATE TABLE `px_po` (
  `po_id` int NOT NULL AUTO_INCREMENT,
  `po_date` date NOT NULL,
  `po_status_id` tinyint(1) NOT NULL,
  PRIMARY KEY (`po_id`),
  KEY `po_date` (`po_date`),
  KEY `po_status_id` (`po_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `px_po` (`po_id`, `po_date`, `po_status_id`)
VALUES
  (39647, '2018-10-09', 3),(39648, '2018-10-09', 3),(39649, '2018-10-09', 3),(39651, '2018-10-09', 3),(39653, '2018-10-09', 3),(39654, '2018-10-09', 3),(39655, '2018-10-09', 3),(39657, '2018-10-09', 3),(39658, '2018-10-09', 3),(39659, '2018-10-09', 3),(39660, '2018-10-09', 3),(39661, '2018-10-09', 3),(39662, '2018-10-10', 3),(39663, '2018-10-10', 3),(39665, '2018-10-10', 3),(39666, '2018-10-10', 3),(39667, '2018-10-10', 3),(39668, '2018-10-10', 3),(39669, '2018-10-10', 3),(39670, '2018-10-10', 3),(39671, '2018-10-10', 3),(39672, '2018-10-10', 3),(39673, '2018-10-10', 3),(39674, '2018-10-10', 3),(39675, '2018-10-11', 3),(39676, '2018-10-11', 3),(39677, '2018-10-11', 3),(39678, '2018-10-11', 3),(39679, '2018-10-11', 3),(39680, '2018-10-11', 3),(39681, '2018-10-15', 3),(39682, '2018-10-15', 3),(39683, '2018-10-15', 3),(39684, '2018-10-15', 3),(39685, '2018-10-15', 3),(39686, '2018-10-15', 3),(39687, '2018-10-15', 3),(39688, '2018-10-15', 3),(39689, '2018-10-15', 3),(39690, '2018-10-15', 3);

Problem query:

SELECT
  po.po_id,
  pst.name AS po_status
FROM px_po po
  LEFT JOIN px_po_status pst ON po.po_status_id = pst.po_status_id
ORDER BY po_date DESC
LIMIT 0,25;

Running EXPLAIN here shows the following:

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | po    | NULL       | index | NULL          | po_date | 4       | NULL |   41 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | pst   | NULL       | ALL   | PRIMARY       | NULL    | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (hash join)   |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+

Workaround #1:

SELECT
  po.po_id,
  (SELECT pst.name FROM px_po_status pst WHERE po.po_status_id = pst.po_status_id) as po_status
FROM px_po po
ORDER BY po_date DESC
LIMIT 0,25;

Oddly, using a subquery performs much better and gets optimized as I'd expect:

+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref                     | rows | filtered | Extra               |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------+
|  1 | PRIMARY            | po    | NULL       | index  | NULL          | po_date | 3       | NULL                    |   25 |   100.00 | Backward index scan |
|  2 | DEPENDENT SUBQUERY | pst   | NULL       | eq_ref | PRIMARY       | PRIMARY | 1       | sandbox.po.po_status_id |    1 |   100.00 | NULL                |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------+

Workaround #2:

I'm also aware that I can leverage optimizer hints to circumvent the poor performance as outlined here: https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-table-level. This yields an identical explain to Workaround #1:

SELECT /*+ NO_BNL() */
  po.po_id,
  pst.name AS po_status
FROM px_po po
  LEFT JOIN px_po_status pst ON po.po_status_id = pst.po_status_id
ORDER BY po_date DESC
LIMIT 0,25;

Couple of notes:

  • The problem seems to disappear when there are 4 or more rows in px_po_status
  • As you might guess, the problem becomes much worse a px_po grows. My production db holds over 100,000+ rows and is unacceptably slow without a workaround.
  • I've built a fiddle to reproduce all of this here: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=28ab6f2cc8c53e86e3539a00604026ef
  • This was somewhat difficult to reproduce! As you can see from the fiddle above, on a fresh import, you need to OPTIMIZE TABLE first or the behaviour is inconsistent.

My Question:

Why does the optimizer seem to fail such a basic operation? Is this a bug?

10
  • Why do you LEFT JOIN px_po_status, if the join columns are both not null and form an RI constraint. This doesn't make sense, and is probably the reason for the plan choice.
    – mustaccio
    Mar 31, 2021 at 16:54
  • See here for plan differences when you change the LEFT JOIN to an (INNER) JOIN.
    – Vérace
    Mar 31, 2021 at 17:13
  • You'll have to test with a realistic dataset - check out what happens here and here.
    – Vérace
    Mar 31, 2021 at 17:20
  • With the FK constraint, the INNER JOIN appears faster...
    – Vérace
    Mar 31, 2021 at 19:54
  • Here is an updated fiddle with a full a realistic dataset (17000+ rows). As you can see, even with INNER JOIN, there is still a filesort happening. For whatever reason, the version they're using here doesn't seem to support NO_BNL(), but on my server it behaves as I mentioned above. db-fiddle.com/f/4VnSqQgz8hMxE2ZDxw6V7c/0 Mar 31, 2021 at 20:01

1 Answer 1

1

I found adding an index around po_status_id and name helps. I dont have a great explanation. I dont know this causes the planner to think it needs to build the entire join despite the limit.

CREATE TABLE `px_po_status` (
  `po_status_id` tinyint NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`po_status_id`),
  key (`po_status_id`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

PS. If I were you I would just use an enum for that status.

3
  • interesting... and weird! i was able to reproduce and confirm it works: dbfiddle.uk/… Apr 2, 2021 at 3:58
  • Yea its strange. I thought it might be because there was no foreign key, but that doesnt seem to be the case.
    – Stephen
    Apr 2, 2021 at 6:24
  • Also note this problem doesn't exist in 5.5 dbfiddle.uk/… Apr 2, 2021 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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