Michael Gale <michael.gale@stripped> wrote on 05/24/2005 04:10:35 PM:
> 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.
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
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.
Unimin Corporation - Spruce Pine