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

Engine independent index statistics are incorrect for large tables on Windows

    XMLWordPrintable

    Details

      Description

      On Windows systems, the engine independend index statistics are incorrect for large tables.
      The problem can be simulated as follows:

      1. install a new MariaDB 10.4.13 server on Windows.

      2. connect to the server and use the "test" schema.

      3. switch on engine independent statistics.

         SET use_stat_tables='preferably';
      

      4. create a table with about 60000 rows.

        create table mydata as select * from information_schema.columns;
        insert into mydata select * from mydata;  
        insert into mydata select * from mydata;
        insert into mydata select * from mydata;
        insert into mydata select * from mydata;
        insert into mydata select * from mydata;
      

      5. create index on TABLE_CATALOG.

         CREATE INDEX AAA ON mydata(TABLE_CATALOG);
      

      6.

      ANALYZE TABLE mydata;
      

      Since the TABLE_CATALOG column always contains the same value, the expectation is that avg_frequency equals rowcount (about 60000).
      This query returns about 60000.

          SELECT AVG(frq) FROM ( 
            SELECT TABLE_CATALOG,COUNT(*) AS frq FROM mydata GROUP BY TABLE_CATALOG
          )a;
      

      7.

      SELECT * FROM mysql.index_stats; 
      

      However, the table mysql.index_stats shows only about 16000 as avg_frequency for the TABLE_CATALOG column.

      On Linux, the values are as expected.
      The wrong values lead to very bad execution plans for larger tables.
      Furthermore my expectation is that Windows and Linux will behave the same.

        Attachments

          Activity

            People

            Assignee:
            varun Varun Gupta
            Reporter:
            lars84 Lars Hansen
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: