List:General Discussion« Previous MessageNext Message »
From:Peter Van Dijck Date:September 21 2006 1:17pm
Subject:Re: Question about LOTS of indexes on a table
View as plain text  
Thanks for the tips. So it seems that:

1) I should index the most often used ones.

I am not sure what OLTP/OLAP means?

Peter

On 9/20/06, Peter Brawley <peter.brawley@stripped> wrote:
>
>  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
>
>
> 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
>
>
>


-- 
find videoblogs: http://mefeedia.com
my blog: http://poorbuthappy.com/ease/
my job: http://petervandijck.net
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