Why the size of MYD file is so high?

Question

While i was creating stress data for a table i found the following files are generated.

-rw-rw---- 1 mysql mysql 8858 Jul 28 06:47 card.frm -rw-rw---- 1 mysql mysql 7951695624 Jul 29 20:48 card.MYD -rw-rw---- 1 mysql mysql 51360768 Jul 29 20:57 card.MYI

Actually i inserted 1985968 number of records in this table. But the index file size is unbelievable.

Structure of the table is

create table card( company_id int(10), emp_number varchar(100), card_date varchar(10), time_entry text, total_ot varchar(15), total_per varchar(15), leave_taken double, total_lop double, primary key (company_id,emp_number,card_date), index (company_id,card_date) );

Is there any way to reduce the filesize of the MYD?

Answer 1

Please note that .MYI is your index, and .MYD is your data. The only way to reduce the size of your .MYD is to delete rows or alter your column sizes.

50MB for an index on 2 million rows is not large.

Let's look at the size breakdown of your table:

• company_id - 4 Bytes
• emp_number - 101 Bytes
• card_date - 11 Bytes
• total_ot - 17 Bytes
• total_per - 17 Bytes
• leave_taken - 9 Bytes
• total_lop - 9 Bytes
• time_entry - avg(length(time_entry)) + 3 Bytes

This gives us a row length of 172 + time_entry bytes. If time_entry averages out at 100 bytes. You're looking at 272 * 2000000 = 544MB

Of significance to me is the number of VARCHARs. Does employee number need to be a varchar(100), or even a varchar at all? You're duplicating that data in it's entirety in your index on (company_id,emp_number,card_date) as you're indexing the whole column.

You probably don't need a varchar here, and you possibly don't need it included in the primary key.

Do you really need time_entry to be a TEXT field? This is likely the biggest consumer of space in your database.

Why are you using varchar(10) for card date? If you used DATETIME you'd only use 8 Bytes instead of 11, TIMESTAMP would be 4 Bytes, and DATE would be 3 Bytes.

You're also adding 1 Byte for every column that can be NULL.

Also try running ANALYZE/REPAIR/OPTIMIZE TABLE commands as well.