List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 17 1999 4:01pm
Subject:Re: Maximum of 16 indexes per table
View as plain text  
Ralph Graulich wrote:
> 
> Hi all,
> 
> i have a larger database of which one specific table consists of
> 
> mysql> SHOW FIELDS FROM dams;
> +-----------------+-----------------+------+-----+---------+----------------+
> | Field           | Type            | Null | Key | Default | Extra          |
> +-----------------+-----------------+------+-----+---------+----------------+
> | horse_id        | int(8) unsigned |      | PRI |         | auto_increment |
> | horse           | varchar(50)     |      | MUL |         |                |
> | horse_country   | char(3)         |      |     |         |                |
> | family          | smallint(2)     |      | MUL |         |                |
> | studbook_ref    | varchar(50)     |      | MUL |         |                |
> | studbook_ref_no | smallint(2)     |      | MUL |         |                |
> | color           | varchar(10)     |      | MUL |         |                |
> | breeder         | varchar(150)    |      | MUL |         |                |
> | day             | smallint(2)     |      | MUL |         |                |
> | month           | smallint(2)     |      | MUL |         |                |
> | year            | int(4)          |      | MUL |         |                |
> | sire            | varchar(50)     |      | MUL |         |                |
> | sire_country    | char(3)         |      |     |         |                |
> | dam1            | varchar(50)     |      | MUL |         |                |
> | dam1_country    | char(3)         |      |     |         |                |
> | sire1           | varchar(50)     |      | MUL |         |                |
> | sire1_country   | char(3)         |      |     |         |                |
> | dam2            | varchar(50)     |      | MUL |         |                |
> | dam2_country    | char(3)         |      |     |         |                |
> | sire2           | varchar(50)     |      | MUL |         |                |
> | sire2_country   | char(3)         |      |     |         |                |
> | dam3            | varchar(50)     |      |     |         |                |
> | dam3_country    | char(3)         |      |     |         |                |
> | sire3           | varchar(50)     |      |     |         |                |
> | sire3_country   | char(3)         |      |     |         |                |
> | alt_sire1       | varchar(50)     |      |     |         |                |
> | alt_sire2       | varchar(50)     |      |     |         |                |
> | alt_sire3       | varchar(50)     |      |     |         |                |
> | alt_sire4       | varchar(50)     |      |     |         |                |
> | alt_sire5       | varchar(50)     |      |     |         |                |
> | alt_sire6       | varchar(50)     |      |     |         |                |
> | alt_sire7       | varchar(50)     |      |     |         |                |
> | alt_sire8       | varchar(50)     |      |     |         |                |
> | alt_sire9       | varchar(50)     |      |     |         |                |
> | alt_sire10      | varchar(50)     |      |     |         |                |
> | alt_sire11      | varchar(50)     |      |     |         |                |
> | alt_sire12      | varchar(50)     |      |     |         |                |
> | alt_sire13      | varchar(50)     |      |     |         |                |
> | alt_sire14      | varchar(50)     |      |     |         |                |
> | alt_sire15      | varchar(50)     |      |     |         |                |
> | alt_sire16      | varchar(50)     |      |     |         |                |
> | alt_sire17      | varchar(50)     |      |     |         |                |
> | alt_sire18      | varchar(50)     |      |     |         |                |
> | alt_sire19      | varchar(50)     |      |     |         |                |
> | alt_sire20      | varchar(50)     |      |     |         |                |
> | changed         | timestamp(14)   | YES  | MUL | NULL    |                |
> +-----------------+-----------------+------+-----+---------+----------------+
> 
> The problem which occurs is concerning the speed. The table needs to be
> sorted for the web often on different fields. To speed the things up I
> generated indexes on most of the fields. But however I can only make up to
> 16 indexes in one table.
> 
> A solution I tried was splitting the table and putting all the "alt_sireX"
> fields in another table, putting all the numerated "dam" and "sire" fields
> in a third table and joining them using horse_id as a unique key field.
> This enabled me to make indexes on all the needed fields, but it even
> slowed down the queries due to the joins.
> 
> Is there any good reason why the amount of indexes is limited to 16? -
> Okay, the more indexes there are, the slower INSERTs are; but for this
> specific problem the table is only read, not written into...
> 
> Any hints?
> 
> Thanks in advance
> 
> Regards,
> ... Ralph ...

Hi Ralph

One problem with using more indexes is, that they all are stored in one file.
This file has to be less than 2-4 GBytes at the moment on most OSs.
So the number of rows you can store in a table will decrease with more of them.

Also larger index files will load slower and eat up more RAM when used.

Obviously there are other reasons too.

Tschau
Christian

PS: Sorry for the late answer, I was on vacation.

Thread
Maximum of 16 indexes per tableRalph Graulich12 Jul
  • Re: Maximum of 16 indexes per tableChristian Mack17 Jul
  • Maximum of 16 indexes per tableMichael Widenius30 Jul
Re: Maximum of 16 indexes per tableTroy Grady30 Jul
  • Re: Maximum of 16 indexes per tableMichael Widenius31 Jul