Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Unanswered: Large tables trouble

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by idmitry
    I have to select e.g. 10 recently modified items.
    two questions:

    1. which 10?

    2. where is the column that determines a "recently modified" item?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    3

    RE: two questions

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select * from items where id in 
    ( select id from items order by lastmodified limit 10)
    will be optimum with an index on lastmodified descending
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2006
    Posts
    3

    Re:

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

    Regard,
    DI

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •