[#MDEV-27242] simple query hangs, appears to use wrong index

Two tables:

MariaDB [opendmarc]> describe messages;

+---------------+---------------------+------+-----+---------------------+----------------+

| Field         | Type                | Null | Key | Default             | Extra          |

+---------------+---------------------+------+-----+---------------------+----------------+

| id            | int(11)             | NO   | PRI | NULL                | auto_increment |

| date          | timestamp           | NO   | MUL | current_timestamp() |                |

| jobid         | varchar(128)        | NO   |     | NULL                |                |

| reporter      | int(10) unsigned    | NO   | MUL | NULL                |                |

| policy        | tinyint(3) unsigned | NO   |     | NULL                |                |

| disp          | tinyint(3) unsigned | NO   |     | NULL                |                |

| ip            | int(10) unsigned    | NO   |     | NULL                |                |

| env_domain    | int(10) unsigned    | NO   |     | NULL                |                |

| from_domain   | int(10) unsigned    | NO   |     | NULL                |                |

| policy_domain | int(10) unsigned    | NO   |     | NULL                |                |

| spf           | tinyint(4)          | NO   |     | NULL                |                |

| align_dkim    | tinyint(3) unsigned | NO   |     | NULL                |                |

| align_spf     | tinyint(3) unsigned | NO   |     | NULL                |                |

| sigcount      | tinyint(3) unsigned | NO   |     | NULL                |                |

| arc           | tinyint(3) unsigned | NO   |     | NULL                |                |

| arc_policy    | tinyint(3) unsigned | NO   |     | NULL                |                |

+---------------+---------------------+------+-----+---------------------+----------------+

16 rows in set (0.001 sec)

MariaDB [opendmarc]> describe domains;

+-----------+--------------+------+-----+---------------------+----------------+

| Field     | Type         | Null | Key | Default             | Extra          |

+-----------+--------------+------+-----+---------------------+----------------+

| id        | int(11)      | NO   | PRI | NULL                | auto_increment |

| name      | varchar(255) | NO   | UNI | NULL                |                |

| firstseen | timestamp    | NO   |     | current_timestamp() |                |

+-----------+--------------+------+-----+---------------------+----------------+

3 rows in set (0.001 sec)

This query hangs and pegs the CPU on the mariadb server at 100%:

select count(*) from messages join domains d1 on messages.from_domain = d1.id join domains d2 on messages.env_domain = d2.id where messages.from_domain='10';

If I change from_domain='10' to from_domain=10 the query no longer hangs.

Note the difference in explain output for the query that hangs vs the query does doesn't:

MariaDB [opendmarc]> explain select count(*) from messages join domains d1 on messages.from_domain = d1.id join domains d2 on messages.env_domain = d2.id where messages.from_domain='10';

+------+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------------------------------------+

| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows  | Extra                                                  |

+------+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------------------------------------+

|    1 | SIMPLE      | d1       | index | PRIMARY       | name | 257     | NULL | 1     | Using index                                            |

|    1 | SIMPLE      | d2       | index | PRIMARY       | name | 257     | NULL | 1     | Using index; Using join buffer (flat, BNL join)        |

|    1 | SIMPLE      | messages | ALL   | NULL          | NULL | NULL    | NULL | 22674 | Using where; Using join buffer (incremental, BNL join) |

+------+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------------------------------------+

3 rows in set (0.001 sec)

MariaDB [opendmarc]> explain select count(*) from messages join domains d1 on messages.from_domain = d1.id join domains d2 on messages.env_domain = d2.id where messages.from_domain=10;

+------+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------------------------------------------+

| id   | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows  | Extra                                           |

+------+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------------------------------------------+

|    1 | SIMPLE      | d1       | const | PRIMARY       | PRIMARY | 4       | const | 1     | Using index                                     |

|    1 | SIMPLE      | d2       | index | PRIMARY       | name    | 257     | NULL  | 1     | Using index                                     |

|    1 | SIMPLE      | messages | ALL   | NULL          | NULL    | NULL    | NULL  | 22674 | Using where; Using join buffer (flat, BNL join) |

+------+-------------+----------+-------+---------------+---------+---------+-------+-------+-------------------------------------------------+

[MDEV-27242] simple query hangs, appears to use wrong index Created: 2021-12-12 Updated: 2023-01-08
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.6.5
Fix Version/s:	None

[MDEV-27242] simple query hangs, appears to use wrong index Created: 2021-12-12 Updated: 2023-01-08