Tuesday, 9 June 2015

How to optimize MySQL table size

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. 


  • 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.


  1. Wow What A Nice And Great Article, Thank You So Much for Giving Us Such a Nice & Helpful Information about Java, keep sending us such informative articles I visit your website on a regular basis.Please refer below if you are looking for best Training Center.
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

  2. Great post! I am actually getting ready to across this information, It's very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.

    Big Data Hadoop Training In Chennai | Big Data Hadoop Training In anna nagar | Big Data Hadoop Training In omr | Big Data Hadoop Training In porur | Big Data Hadoop Training In tambaram | Big Data Hadoop Training In velachery