Re: Specifying many rows in a table

On Wed, Jan 28, 2004 at 01:15:27PM -0700, scott.marlowe wrote:
> On Wed, 28 Jan 2004, Steve Atkins wrote:
>
> > select * from table where id in (1, 3, 5, ...., 100000, 100017, 23000);
>
> > This falls over when it exceeds the maximum expression depth of 10,000.
> > And I have a sneaky feeling that increasing max_expr_depth isn't the
> > right fix.
>
> The optimizations made for in() queries in the 7.4 branch only really work
> when there's a subselect / table in the in. You could try inserting those
> numbers into a temp table and subselecting it.

I think using IN with such a big distinct set would suck performance-
wise, because it would have to resort to the old method which was slow.
I could be mistaken though. If IN (SELECT...) doesn't work, I'd try
rewriting the query to use an EXISTS pulling the values from a temp
table instead ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers