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

MyISAM creates defect indexes on varchar, if rowcount is above some threshold between 480,000,000 and 490,000,000

    XMLWordPrintable

    Details

      Description

      DROP TABLE IF EXISTS test;
      CREATE TABLE test (
      rs	VARCHAR(20) PRIMARY KEY
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
      

      rs is in the form "rs12364789", max. length of the string is 12; always "rs" before a number

      # myisamchk --keys-used=0 -rq test.MYI
      ... FLUSH
      # mysqlimport --local --fields_escaped_by=\\ -u root -p test_db test.tsv
      ... FLUSH
      # myisamchk --analyze --update-state --sort_buffer_size=56G  --recover --quick --quick --tmpdir=/mnt/WORK/tmp test.MYI
      ... FLUSH
      

      RESULTS:

      • with 480,000,000 rows everything is OK
      • with 490,000,000 rows nothing will be found.
        EXPLAIN says: "Impossible WHERE noticed after reading const tables"

      The row can still be found with IGNORE INDEX (PRIMARY)

      If I build another table with only a non unique index on `rs` , EXPLAIN says it's using the index but the query won't find anything..

      A server version from 3 or 4 Months ago worked OK.

      Hope it helps

      Thanks

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              rucha174 Rucha Deodhar
              Reporter:
              rawi rawi
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: