List:General Discussion« Previous MessageNext Message »
From:SGreen Date:May 24 2005 8:27pm
Subject:Re: data length vs index length ??
View as plain text  
Michael Gale <michael.gale@stripped> wrote on 05/24/2005 04:10:35 PM:

> Hello,
> 
> 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.
> 
> Michael
> 

This makes excellent sense. An index is physically stored as a list of 
values (just as they exist in the table or as just the first n characters 
of a value (called a prefix)) and a pointer back into the database (where 
the record is physically located within the datafile). Depending on how 
many indexes you have on your tables, how you constructed your indexes, 
and the physical shape of the data being indexed, it is possible that your 
indexes will take up space that is MUCH larger than the table(s) they are 
built from. 

To reduce the disk space consumed by your indexes, try these tips: 

a: Create the fewest number of indexes you need to keep your application 
performing at an acceptable rate. 
b: Use multi-column indexes when practical to do so. 
c: Avoid index duplication. What I mean is that if you have an index on 
(id, name), you shouldn't create another index on (id, date) unless it 
really helps your performance.
d: Tune your queries whenever practical to use existing indexes to avoid 
creating new ones especially for seldom run queries.

Basically we pay a price in disk space for query performance. Faster 
results generally means more space consumed. The down-side is the more 
indexes you have on a table, the slower an INSERT or DELETE will be as 
either command requires updating all of your indexes for each and every 
row added or removed.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


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