We have to design your tables in such a way to use minimize their space on the disk. This outcome is reducing to read and write data from disk. Create table can take very less space, but while their content actively processed at the time of execution query.
MySQL supports many verying storage engines and row format. Choosing the right table format for your application can give you a high performance.
You can get higher performance for a table and minimize storage space by using the techniques listed below
- Use the smallest data types. MySQL has many specialized types that save disk space and memory. Let’s take example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.
- Declare columns to be NOT NULL if possible. It makes SQL operations faster
Row Format
- InnoDB tables use a compact storage format. By default, tables are created in the compact format (ROW_FORMAT=COMPACT). The compact row format reduce row storage space by about 20% at the cost of increasing CPU use for some operations
- For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used. This is faster but may waste some space.
Indexes
- Create only the indexes that you need to improve query performance. Indexes are good for retrieval, but slow down insert and update operations. If you access a table mostly by searching on a combination of columns, create a single composite index on them rather than a separate index for each column. The first part of the index should be the column most used. If you always use many columns when selecting from the table, the first column in the index should be the one with the most duplicates, to obtain better compression of the index.
