I've got two very large tables: the first one is designated for some items and called 'item' and the second depicts access control rules on these items and called 'acentry'. The relationship is 'item'-'acentry' = one-to-many.
I have to select e.g. 10 recently modified items.
Possible solutions known 4me are:
1. Join 'item' and 'acentry' tables, sort the result and retrieve top items using DBMS-specific limiting keyword (LIMIT for MySQL, TOP - for MSSQL, etc.). This demands stroring the full 'item' table in the DBMS memory for each request - not good.
2. Use additional table for storing cerently used items. This approach is hard to implement, 'cause it conflicts with security rigths. Some user can have no permission to that 10 items stored while she has access to other recently modified items. Storing 10 items per each user demands some complicated logics when access control on some items are changed.
3. Use some stored proc which opens cursor for 'item' table, iterate thru all the items and finds 10 recently used which are allowed for the certain user. This demands again storing the full 'item' table in DBMS memory.

Does some another approach exits? I mean e.g. iterate tru entire 'item' table not using indexes and retriving corresponding 'acetry' rows without putting the full 'item' table into DBMS memory or always keep it in memory sharing among all the requests?

two questions:

1. which 10?

2. where is the column that determines a "recently modified" item?

There is a column 'lastmodified' of type DATE in 'item' table. Using this column I select 10 recently modified items.

will be optimum with an index on lastmodified descending

Thank's, that's really fast!
The trick is in dummy nesting request which 'explains' DBMS desired by me way of processing.

Regard,
DI