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 <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
