Results 1 to 7 of 7
  1. #1
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26

    Indexing Foreign Keys?

    Hi All,

    I am attempting to improve performance on my DB by introducing indexes for commonly searched/ filtered fields. I know in Oracle indexing foreign keys is a necessity, and was curious if Access indexes foreign keys on its own. When selecting foreign key in design view it doesn't say it is indexed, so just wanted to check if this is done behind-the-scenes and if this is a also a necessity in Access.



    Thank you,
    Skid

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    AFAIK, a field is not indexed unless it is designated as primary key or set for indexing in table design.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Pretty sure not; and a best practice is to index fields that you search on; don't set the index property to true on an autonumber field when it's not a PK. If autonumber is a PK, it's automatically indexed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26
    I ended up indexing my foreign keys via design view. I can't quite tell if performance has been improved.. if so, its relatively minor. Anyways, thanks for the replies! I may run a couple trial queries with and without the indexes to see if performance differs.

    Thanks!
    Skid

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Indexing can make a dramatic difference to search times especially for large recordsets.
    But you need to ensure that the search fields are not only indexed but that the index is actually used in the search.
    For example if a date field is indexed and you want to search for all records in the year 2018, filtering by Year(DateField)=2018 doesn't allow the indexing to be used
    However searching for dates between #01/01/2018# And #31/12/2018# uses the index and will be much faster.

    For more info, I suggest you lookup SARGABLE queries.
    Last edited by isladogs; 12-10-2019 at 11:50 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26
    Thank you for the insight, I am going to do some research on those queries to make sure I am properly searching for my indexed fields.

    Thank you!
    Skid

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome.
    You may find this article from my website useful: Optimise Queries
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Foreign keys
    By Accessuser67 in forum Access
    Replies: 1
    Last Post: 10-05-2015, 06:33 PM
  2. primary and foreign keys?
    By themebuddies in forum Access
    Replies: 1
    Last Post: 12-14-2014, 09:23 PM
  3. Extracting Foreign Keys
    By QuantifyRisk in forum Forms
    Replies: 7
    Last Post: 08-08-2014, 02:05 PM
  4. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  5. Need help with foreign keys
    By acdougla17 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 03:50 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums