[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1088
  • Last Modified:

Reducing an index size

I am restructuring my databse to store data more efficently.

I had a table with a primary index made up of an int and a varchar(30) column. By using lookup tables, I altered the varchar column to a smallint. By my cacluations, the index size should be reduced to about one third of the original, but it only went down to two thrids, when looking at index length in MySQL administrator.

Does this seem right, or is the database allocating chunks of space to the index and not releasing it all? Optimizing the table made no difference.

Kim Ryan
Kim Ryan
1 Solution
What happen if you re-build the whole index by deleting the old one and add a new one (try it in a test-db) ?
What was the average length of a field in your varchar(30) table?
Int is 4 bytes, smallint is 2 bytes, varchar(30) is length+1 bytes.

The old index was length+5+overhead bytes per entry, and the new one is a constant 6+overhead bytes per entry.  You also have to figure that there is something like 4-8 bytes of overhead per entry (to point back at the database file), so if the average length of the varchar field was 7 characters, 2/3 would be just about right.

Kim RyanIT ConsultantAuthor Commented:
Thanks, good explanation. I have a better understanding of  indexing overhead now. Average length of the varchar column was about 10.

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now