List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:September 21 2006 3:24pm
Subject:Re: Question about LOTS of indexes on a table
View as plain text  
Peter

I am not sure what OLTP/OLAP means?

T=transaction (processing is mostly inserts, updates), 
A=Analysis(processing is mostly for reports), see 
http://wiki/en/wikipedia.org/OLAP. You keep 2 versions of your data, one 
optimised for inserts/updates, one optimised for reporting, you update 
the latter from the former as often as your report consumers need 
up-to-dateness, and you route queries to the appropriate db. The 
differentiation can be whole-hog (2 dbs), or it can be partial, ie a few 
denormalised report-optimised tables in one db.

PB

-----

Peter Van Dijck wrote:
> 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
>>
>>
>>
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.12.6/453 - Release Date: 9/20/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