Ragnar Hafstað escreveu:
>>[how to solve the get next 100 records problem]
>
>>I tried that. It does not work in the generic case: 6 MegaRec, telephone
>>listing, alphabetical order.
>
> lets say pkey is your primary key and skey is your sort key, and
> there exists an index on (skey,pkey)
>
> your first select is
> select ... from tab ORDER by skey,pkey LIMIT 100;
>
> your subsequent selects are
> select ... from tab WHERE skey>skey_last
> OR (skey=skey_last AND pkey>pkey_last)
> ORDER BY skey,pkey
> LIMIT 100 OFFSET 100;
I tied that, it is veeery slow, probably due to the OR operand :(
BUT, I think that this is close to a final solution, I made some
preliminary test ok. Please tell me what you think about this.
Fisrt let's state that I am reading records to put on a screen (in a
Table/Grid). I separated the problem is *3* parts
-first select is as above:
select ... from tab ORDER by skey,pkey LIMIT 100;
-second method for next 100:
select ... from tab WHERE skey>=skey_last ORDER BY skey,pkey LIMIT 100;
but here I test for repetitions using pkey and discard them
-now if I get all repetitions or the last 100 have the same skey with
the second method, I use
select ... from tab WHERE skey=skey_last AND pkey>pkey_last ORDER BY skey,pkey
LIMIT100;
until I get an empty response, then I go back to the second method.
All queries are extremely fast with 6000000 records and it looks like
the few redundant or empty queries (but very fast) will not be a problem.
What is your opinion about this (apart that it is a bit complex :) ??
Alain
