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
(lastname,firstname).

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
	dnelson@stripped
Thread
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