0

When i pulled up my top 5 mysql tables in terms of size, i could see the the index_length is higher compared to data_length.

I have a same table(same schema) for the same application in SQL Server and the index size is smaller than the data size.

data : 167046824 KB
Index : 41807080 KB

Can anyone please explain me this behavior and looking at the DATA_FREE value i don't think there is much space to be reclaimed by optimize table or other options.

Sample schema and index details in MySQL

+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| table_id    | int(10)       | NO   | MUL | NULL    |       |
| sampletime  | datetime      | NO   | MUL | NULL    |       |
| samplevalue | decimal(28,2) | YES  |     | NULL    |       |
| samplestdev | decimal(28,2) | NO   |     | NULL    |       |
| samplerate  | decimal(28,2) | NO   |     | NULL    |       |
| samplemax   | decimal(28,2) | NO   |     | NULL    |       |
| tz_offset   | int(10)       | NO   |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

 TABLE_CATALOG: def
TABLE_SCHEMA: 
 TABLE_NAME: 
 TABLE_TYPE: BASE TABLE
     ENGINE: InnoDB
    VERSION: 10
 ROW_FORMAT: Dynamic
 TABLE_ROWS: 1675339068
AVG_ROW_LENGTH: 99
DATA_LENGTH: 165876416512
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 213572026368
  DATA_FREE: 502267904
AUTO_INCREMENT: NULL
CREATE_TIME: 2019-04-09 00:43:16
UPDATE_TIME: 2019-04-09 10:39:06
 CHECK_TIME: NULL
TABLE_COLLATION: utf8_unicode_ci
   CHECKSUM: NULL
CREATE_OPTIONS: partitioned
TABLE_COMMENT:

---------+
| Table            | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type
+------------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
| table1 |          1 | table1_Idx0 |            1 | sampletime  | A         |     7756040 |     NULL | NULL   |      | BTREE      
| table1 |          1 | table1_Idx0 |            2 | table_id    | A         |  1600613120 |     NULL | NULL   |      | BTREE      
| table1 |          1 | table1_Idx1 |            1 | table_id    | A         |     7199898 |     NULL | NULL   |      | BTREE      
| table1 |          1 | table1_Idx1 |            2 | sampletime  | A         |  1675100672 |     NULL | NULL   |      | BTREE      
| table1 |          1 | table1_Idx1 |            3 | tz_offset   | A         |  1675100672 |     NULL | NULL   |      | BTREE      
| table1 |          1 | table1_Idx1 |            4 | samplerate  | A         |  1675100672 |     NULL | NULL   |      | BTREE      
| table1 |          1 | table1_Idx1 |            5 | samplevalue | A         |  1675100672 |     NULL | NULL   | YES  | BTREE      

Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.