Finally got some time to give some useful advice for all developers dealing with MySQL.
Problem Scenario -
I had a table with schema like this -
I deleted some 1.2 lac records on the basis of date and when I retrieved the table size using this query I got exactly the same size of table before deleting.
After removing the 'DATA_FREE' from the same.
There I came to pause and ponder that why the free space is still unusable ?
Reason -
'DELETE' invalidates the record and size of the records used to occupy data. it may be reused later. This problem is quite similar to de-fragmentation of disk drive in windows to improved the disk efficiency.
Also, this scenario is common with the tables using variable length rows having column types like 'VARCHAR', 'TEXT', 'BLOB', 'TEXT', 'VARBINARY'.
Solution -
Meanwhile this cannot be stated as a problem cause according to MySQL guys - "Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions".
However if you want to get that space now for peace of mind and soul, so you can run 'OPTIMIZE' on the table like -
Remember 'OPTMIZE' works only on 'MyISAM' and 'InnoDB' engines. So this can be done in manner when ever you deleting the records from the table either manually or by CRON job place one more query to get this done.
Addon -
Posting the query to get the size of all the tables with sorting on the basis of size with exact data and after removing data free.
Thanks and will be posting more ...
