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

too large INDEX_LENGTH

    XMLWordPrintable

Details

    Description

      We have a table with integer fields, it has too large index space. When I copy data to new table the new table significantly less then origin. Data is append mostly, no update.

       CREATE TABLE `mobile_app_user_log` (
        `id_soul` int(10) unsigned NOT NULL DEFAULT '0',
        `id_user` int(10) unsigned NOT NULL DEFAULT '0',
        `id_app` int(10) unsigned NOT NULL DEFAULT '0',
        `id_device` int(10) unsigned NOT NULL DEFAULT '0',
        `id_device_model` int(10) unsigned NOT NULL DEFAULT '0',
        `id_version` int(10) unsigned NOT NULL DEFAULT '0',
        `id_code_version` int(10) unsigned NOT NULL DEFAULT '0',
        `id_os` int(10) unsigned NOT NULL DEFAULT '0',
        `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `date_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        PRIMARY KEY (`id_user`,`id_soul`,`id_app`,`id_device`,`id_device_model`,`id_version`,`id_code_version`,`id_os`),
        KEY `id_user` (`id_user`) USING BTREE,
        KEY `date_update` (`date_update`) USING BTREE,
        KEY `date_update_cover` (`id_user`,`date_update`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
      

      > create table tmp.ss like api.mobile_app_user_log;
       
      > insert into  ss select * from api.mobile_app_user_log;
      Query OK, 9295572 rows affected (5 min 40.75 sec)
      Records: 9295572  Duplicates: 0  Warnings: 0
       
      > select * from information_schema.tables  where table_name in ('mobile_app_user_log', 'ss')\G;
      *************************** 1. row ***************************
        TABLE_CATALOG: def
         TABLE_SCHEMA: api
           TABLE_NAME: mobile_app_user_log
           TABLE_TYPE: BASE TABLE
               ENGINE: InnoDB
              VERSION: 10
           ROW_FORMAT: Compact
           TABLE_ROWS: 8260584
       AVG_ROW_LENGTH: 124
          DATA_LENGTH: 1030750208
      MAX_DATA_LENGTH: 0
         INDEX_LENGTH: 51393871872
            DATA_FREE: 5242880
       AUTO_INCREMENT: NULL
          CREATE_TIME: 2017-12-22 00:04:39
          UPDATE_TIME: NULL
           CHECK_TIME: NULL
      TABLE_COLLATION: utf8_general_ci
             CHECKSUM: NULL
       CREATE_OPTIONS: 
        TABLE_COMMENT: 
      *************************** 2. row ***************************
        TABLE_CATALOG: def
         TABLE_SCHEMA: tmp
           TABLE_NAME: ss
           TABLE_TYPE: BASE TABLE
               ENGINE: InnoDB
              VERSION: 10
           ROW_FORMAT: Compact
           TABLE_ROWS: 9030468
       AVG_ROW_LENGTH: 64
          DATA_LENGTH: 580911104
      MAX_DATA_LENGTH: 0
         INDEX_LENGTH: 1501560832
            DATA_FREE: 6291456
       AUTO_INCREMENT: NULL
          CREATE_TIME: 2018-04-24 15:44:16
          UPDATE_TIME: NULL
           CHECK_TIME: NULL
      TABLE_COLLATION: utf8_general_ci
             CHECKSUM: NULL
       CREATE_OPTIONS: 
        TABLE_COMMENT:
      

      # ls -l api/mobile_app_user_log.* tmp/ss.*
      -rw-rw---- 1 mysql mysql        8944 Apr 24  2017 api/mobile_app_user_log.frm
      -rw-rw---- 1 mysql mysql 53208940544 Apr 24 15:54 api/mobile_app_user_log.ibd
      -rw-rw---- 1 mysql mysql        2682 Apr 24 15:44 tmp/ss.frm
      -rw-rw---- 1 mysql mysql  2139095040 Apr 24 15:50 tmp/ss.ibd
      

      How is it possible? Pages fragmentation?

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              chernomorets Sergey Chernomorets
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.