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

after upgrading MariaDB 10.2 to 10.6 WHERE IN () doesn't use index for BIGINT

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 10.6
    • N/A
    • None
    • None

    Description

      A table with more than 650,000,000 rows does not use an index after updating MariaDB from 10.2 to 10.6

      CREATE TABLE `parts` (
        `mainid` int(10)    UNSIGNED NOT NULL,
        `pid`    bigint(20) UNSIGNED NOT NULL,
        `name`   varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
        `dt`     date                NOT NULL
      ) 
       
      ALTER TABLE `parts`
        ADD PRIMARY KEY (`mainid`),
        ADD KEY `pid` (`pid`);
      

      Maria uses an index when my queries have WHERE pid IN ( n1 ), but it doesn't use an index when my queries have WHERE pid IN ( n1, n2, n3 ).

      Same query in MariaDB 10.2 (1.jpg) and MariaDB 10.6 (2.jpg)

      EXPLAIN
      SELECT
          *
      FROM
          parts
      WHERE
          pid IN (
              6826931823469672941,
              2750571406036799415,
               156705013816687978
          );
      

      Curiously, if I put the bigint literals in quotes it uses a better plan: (3.jpg)

      EXPLAIN
      SELECT
          *
      FROM
          parts
      WHERE
          pid IN (
              '6826931823469672941',
              '2750571406036799415',
               '156705013816687978'
          );
      

      I tried to change eq_range_index_dive_limit to 0 (value in 10.2) from 200 (value in 10.6) in my.cnf, but it didn't help. Same with the maximum value of 4294967295.

      Can I use single quotes for BIGINT? As I understand it, it's the same if I don't use them

      Attachments

        1. 3.jpg
          3.jpg
          24 kB
        2. 2.jpg
          2.jpg
          26 kB
        3. 1.jpg
          1.jpg
          22 kB

        Activity

          People

            Unassigned Unassigned
            johny1960 johny
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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