Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24137

Optimizer choosing the wrong index despite of the PK showing better explain

    XMLWordPrintable

Details

    Description

      We have the following table:

      CREATE TABLE `templatelinks` (
        `tl_from` int(8) unsigned NOT NULL DEFAULT 0,
        `tl_namespace` int(11) NOT NULL DEFAULT 0,
        `tl_title` varbinary(255) NOT NULL DEFAULT '',
        `tl_from_namespace` int(11) NOT NULL DEFAULT 0,
        PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
        KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
        KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
      ) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
      

      We have run an analyze table to refresh all the stats.
      Having the following query: (pasted here as otherwise it goes above the characters limit for bugs reporting: https://phabricator.wikimedia.org/P13194

      The explain for it:

      mysql:root@localhost [cswiki]> show explain for 73439158;
      +------+-------------+---------------+------+----------------------+--------------+---------+-------+---------+--------------------------+
      | id   | select_type | table         | type | possible_keys        | key          | key_len | ref   | rows    | Extra                    |
      +------+-------------+---------------+------+----------------------+--------------+---------+-------+---------+--------------------------+
      |    1 | SIMPLE      | templatelinks | ref  | PRIMARY,tl_namespace | tl_namespace | 4       | const | 1294541 | Using where; Using index |
      +------+-------------+---------------+------+----------------------+--------------+---------+-------+---------+--------------------------+
      1 row in set, 1 warning (0.000 sec)
      

      However, using a hint like this, changes the query plan drastically:

      SELECT / tl_from, tl_title FROM `templatelinks` USE INDEX (PRIMARY) WHERE....
      

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	templatelinks	range	PRIMARY	PRIMARY	265	NULL	2480	Using where; Using index
      

      However, the optimizer keeps choosing the tl_namespace.

      This is the optimizer trace for the first and original query: https://phabricator.wikimedia.org/P13220

      And the same but with the hint to use the PK: https://phabricator.wikimedia.org/P13221

      At a first glance we can see the large difference on scanned rows (and run time):
      What the optimizer picks by default

          "r_total_time_ms": 162170,
          "table": {
            "table_name": "templatelinks",
            "access_type": "ref",
            "possible_keys": ["PRIMARY", "tl_namespace"],
            "key": "tl_namespace",
            "key_length": "4",
            "used_key_parts": ["tl_namespace"],
            "ref": ["const"],
            "r_loops": 1,
            "rows": 1294541,
      

      And the runtime and rows if we force the PK to be used:

         "r_total_time_ms": 8.3322,
          "table": {
            "table_name": "templatelinks",
            "access_type": "range",
            "possible_keys": ["PRIMARY"],
            "key": "PRIMARY",
            "key_length": "265",
            "used_key_parts": ["tl_from", "tl_namespace", "tl_title"],
            "r_loops": 1,
            "rows": 2480,
      

      The query run time is way different with and without the hint:
      And this is true if we try a `USE (PRIMARY) on the query shows way better results:

      265 rows in set (0.146 sec)
      

      And this is NOT doing USE (PRIMARY) and just leaving the query choosing whatever it prefers (tl_namespace);

      265 rows in set (2 min 9.220 sec)
      

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            marostegui Manuel Arostegui
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.