Peter
/>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? /
It does not make sense for inserts and updates, but it sure makes sense
for reproting, so have you considered separating your functionality into
OLTP and OLAP dbs?
PB
-----
Peter Van Dijck wrote:
> 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
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.12.5/451 - Release Date: 9/19/2006
