Thread: 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