I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer
For an example we have a table City with InnoDB engine,lets say in future (in next 1 year) it will have 1 million of records so what will be the estimated data size and index size of that table in this period.
mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)
UPDATE
What will be the estimated upper bound (Max size of table) with 1 million records and how can we estimate it.
Best Answer
Given the table description, I see
For a million rows, that would 77,000,000 bytes (73.43 MB)
As for measuring the table, for a given table mydb.mytable, you can run this query
To measure all tables grouped by Database and Storage Engine
Run these queries and you can track changes in database/engine disk usage.
Give it a Try !!!