List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 18 2000 5:29am
Subject:Re: how to increase number of indices on tables
View as plain text  
In the last episode (Jan 17), Noah Friedman said:
> I have an application that requires a lot of indexes on a table; more
> than the 16 that MySQL 3.22 seems to allow.  Ultimately, I don't know
> how many I may need; ideally I would like it to be arbitrary
> depending on what the clients end up searching for.  But I would
> guess that 128 or 256 indexes would satisfy our requirements for a
> while.


> How quickly does *search* performance degrade with more indexes to
> the point where there is no benefit to having an index at all?  I do
> not expect frequent inserts or deletes, but I expect many searches on
> arbitrary column sets.

Note that mysql will only use one index per table for any given query.
Separate b-tree indexes can not be easily combined.  Oracle (and
possibly others) have the concept of 'bitmap' indexes, which can be
combined in one query, but Mysql does not have this index type, and
they have their own drawbacks.

If you have arbitrary WHERE columns, index the most common fields with
single indexes, and determine which groups of fields will be used
together and create compound indexes on them.

How many records are you looking at, how large are your result sets,
and how fast is your I/O system?

	Dan Nelson
how to increase number of indices on tablesNoah Friedman18 Jan
  • Re: how to increase number of indices on tablesDan Nelson18 Jan
    • Re: how to increase number of indices on tablesNoah Friedman19 Jan
  • Re: how to increase number of indices on tablesColin McKinnon18 Jan