Re: Select where id in (LARGE LIST) ?

Jasper,

You can chunk your operation. That means to only use 100 entries in one
run, and repeat it until all ids were processed. Use a prepared
statement, that will save you some overhead. The last chunk will have
less entries than the parameter placeholders, so you will have to build
a special last statement, or to set the superfluous parameters to null
or to one of the values from the last chunk, depends on what kind of
query you have. We do all our data import/export this way, and it works
fine.

Cheers,
Csaba.

On Fri, 2005-07-29 at 17:30, Jasper Potts wrote:
> I am working on a gui application which has a list of ids of selected
> items. To perform an operation on the selected items I do a
> "select/update .. where id in(...)". There seems to be a limit of
> 100-200 items in the list. Is there a way of doing this with large
> lists, maybe 10,000, 100,000 long?
>
> The best solution I have so far is to create a selection table and write
> the select out to that and perform a join but the 100,000 inserts are slow.
>
> The ids are int8(long), so not talking large amounts of data, couple Mb
> at most. Database and client running on same machine over localhost.
>
> Many Thanks
>
> Jasper
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match