List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:September 21 2006 3:28am
Subject:Re: Question about LOTS of indexes on a table
View as plain text  
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
Thread
Question about LOTS of indexes on a tablePeter Van Dijck21 Sep
  • Re: Question about LOTS of indexes on a tablePeter Brawley21 Sep
    • Re: Question about LOTS of indexes on a tablePeter Van Dijck21 Sep
      • Re: Question about LOTS of indexes on a tablePeter Brawley21 Sep
RE: Question about LOTS of indexes on a tableQuentin Bennett21 Sep