Re: Specifying many rows in a table

On Wed, 28 Jan 2004, Steve Atkins wrote:

> I have a large table (potentially tens or hundreds of millions of rows) and
> I need to extract some number of these rows, defined by an integer primary
> key.
>
> So, the obvious answer is
>
> select * from table where id in (1,3,4);
>
> But I may want to extract a large number of rows, many thousands
>
> select * from table where id in (1, 3, 5, ...., 100000, 100017, 23000);

If the ranges are sequential, then between would work. I have a feeling
that they aren't though, looking at your examples.

> 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.