Michael Gale <[EMAIL PROTECTED]> 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
