hello,

I have a table (BIGTABLE) with 2 fields
UserID
RandomSeed

The data are :

8 000 000 total records
6 000 000 records where userID = 'CA'
1 000 000 records where userID = 'LT'
100 records where userID = 'K323'
100 records where userID = 'K213'
100 records where userID = 'K643'
....

randomseed is random number

i create this index :

CREATE DESCENDING INDEX SEARCH_1_IDX ON BIGTABLE (UserID, RandomSeed);

now if i do


Select First 201 skip 0
UserID,
randomSeed
FROM BIGTABLE
where
UserID = 'CA'
order by randomseed desc

stats: 5 800 000 indexed read !!
very slow > 10 Seconds !!

********

Select First 201 skip 0
UserID,
randomSeed
FROM BIGTABLE
where
USERID = 'CA' AND
RANDOMSEED > 3223333423
order by randomseed desc

stats: 1300 indexed read !!
very fast < 10 ms !!



so why in the previous request, Whith
Select First 200 skip 0 it's look like that
the engine scan all the table (5 800 000 indexed read)
when i want only the First 200 ? like you see
in the second query if i force que engine
to return only the first 200 records by a filter
instead by the first 200 skip 0 it's very
fast...

is it a normal behavior or simply an feature (or bug in someway) that is in the todo list to be optimized ?

thanks you by advance
stephane