Toggle Categories

MySQL tables size optimization

August 09, 2019

 Given: we have a table with 7000 rows and 15 columns

Test cases with VARCHAR field:

1. NULL allowed
value is '' (empty string) (SET field_name = '')
Table size: 2,320 KB

2. NULL allowed
value is NULL (empty string) (SET field_name = NULL)
Table size: 2,320 KB

3. NULL is not allowed
value is '' (empty string) (SET field_name = '')
Table size: 2,608 KB

Conclusion: When storing VARCHAR field, allow NULL and store NULL or empty string.
Open question: what will be working faster when doing searches by this field?

Test cases with unsigned INT field:

1. NULL allowed
value is NULL (SET field_name = NULL)
Table size: 2,320 KB

2. NULL allowed
value is 0 (SET field_name = '0')
Table size: 2,320 KB

3. NULL is not allowed
value is 0 (SET field_name = '0')
Table size: 2,608 KB

Conclusion: When storing unsigned INT allow NULL and store NULL or 0.
Open question: what will be working faster when doing searches by this field?

 

Script used for table size:

SELECT 
     table_schema AS `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024), 2) `Size in Kb`,
NOW()
FROM information_schema.TABLES 
WHERE table_name = 'table_name'
ORDER BY (data_length + index_length) DESC;