Обсуждение: Very long "or" where clause

Hi,
from a table with 100,000,000 rows I have to sum some records using a
query like:

select sum(field1) from mytab where
  (time = 1 and id = 3)
or
  (time = 3 and id = 1)
or
  (time = 2 and id = 5)
[...]


The "or clauses" can be even 10,000,000...
Which would be the best method to access data? Should I use a procedure
on the server side?

* Scara Maccai:

> Which would be the best method to access data? Should I use a
> procedure on the server side?

I tend to use a join to a temporary table for similar purposes.  It
seems like the cleanest approach.

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

Scara Maccai wrote:
> Hi,
> from a table with 100,000,000 rows I have to sum some records using a
> query like:
>
> select sum(field1) from mytab where
>  (time = 1 and id = 3)
> or
>  (time = 3 and id = 1)
> or
>  (time = 2 and id = 5)
>
> The "or clauses" can be even 10,000,000...
> Which would be the best method to access data? Should I use a procedure
> on the server side?

Put the test-values into a temporary table, analyse it and then join
against it. Can't say about indexes without knowing more about your
usage pattern.

--
   Richard Huxton
   Archonet Ltd

> Put the test-values into a temporary table, analyse it and then join
> against it.

Ok, I didn't think of it.

> Can't say about indexes without knowing more about your
> usage pattern.


What do you mean?

Scara Maccai wrote:
>> Put the test-values into a temporary table, analyse it and then join
>> against it.
>
> Ok, I didn't think of it.
>
>> Can't say about indexes without knowing more about your usage pattern.
>
>
> What do you mean?

You might want an index on time, id, (id,time) or (time,id) - depends on
how many OR clauses there are typically and how many distinct values
there are for "time" and "id".

--
   Richard Huxton
   Archonet Ltd