List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 24 2005 8:40pm
Subject:Re: data length vs index length ??
View as plain text  
In the last episode (May 24), Michael Gale said:
> Currently I have a large mysql table (36 million rows) and according
> to the mysql-admin the data length is 6.5GB and my index length is
> 8.8GB.
> I am new to mysql, but I would assume that my index should not
> consume more space then my data ?
> But in order to provide the data in a timely manner I do not believe
> I can remove any of my indexes as there were all added as a result of
> a query taking 30-40 minutes.

If you have multiple-column indexes and they overlap, you can easily
end up with more space used up in indexes than data.  For example: two
indexes, one on (firstname,lastname), and the other on

Also, if you have a lot of deletes and updates, your indexes will end
up with unused space in the index blocks.  The table space freed up by
a deleted row can be reused by any row, but if you are indexing on last
name, the space freed up in an index block by deleting a row with
"Smith" in it can only be filled by another "Smith".  That's why it's a
good idea to run "optimize table" occasionally if you make lots of
changes to your tables.  That will rebuild your indexes and remove the
slack space.

	Dan Nelson
data length vs index length ??Michael Gale24 May
  • Re: data length vs index length ??mfatene24 May
  • Re: data length vs index length ??Dan Nelson24 May
  • Re: data length vs index length ??SGreen24 May
  • classic outer join problemHank24 May
    • Re: classic outer join problemSGreen24 May
    • Re: classic outer join problemmfatene25 May