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
