Reducing an index size

Posted on 2006-05-21
Last Modified: 2010-05-18
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.

Question by:teraplane
    LVL 1

    Expert Comment

    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) ?
    LVL 22

    Accepted Solution

    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.

    LVL 19

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now