If the combination of fields that will be subject to 'where' is unknown, and will be unknown forever, then I think you have no choice but to index each one individually and let MySQL make the choice as to which one to use.

If, however, you know, or can establish, that certain combinations of 'f' will be used more often then others, then adding indices for those combination will be useful.

If the table is read-intensive, then having multiple indices will improve performance (up to a point), but if its write-intensive, then adding indices will slow things down.

HTH

Quentin

-----Original Message-----
From: Peter Van Dijck [mailtoetervandijck@gmail.com]
Sent: Thursday, 21 September 2006 3:14 p.m.
To: MYSQL General List
Subject: Question about LOTS of indexes on a table


Hi,
I've been trying to figure this out for a while..

I have a table ITEMS with about 15 fields that can be used in any
combination in where queries, let me call these fields f1 to f15.
There are also 3 fields used for ordering, let's call them o1 to o3.

So the table is:
tablename (id, title, f1, f2, f3, f4, ..., f15, o1, o2, o3)

f1 to f15 are all int(11). The table contains, let's say, 50,000 rows.

The queries sent against this table can combine up to 4 f-fields, and
1 ordering field.

So it could be:
- SELECT * FROM table WHERE f3=x AND f7=x AND f12=x ORDER BY o3 DESC
and so on.. you get the idea.

The question is: we need indexes to make this go fast. How many
indexes do we need?

It doesn't seem like it would make sense to make an index for every
possible combination... but there must be a way to do this
intelligently?

Any input is very welcome! I hope I explained the problem clearly?

Thanks,
Peter

--
find videoblogs: http://mefeedia.com
my blog: http://poorbuthappy.com/ease/
my job: http://petervandijck.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=q...infinity.co.nz
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.