Results 1 to 8 of 8
  1. #1
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128

    Question on proper table indexing to speed up queries

    If my query has an ORDER BY clause that spans multiple tables, then what kind of table indices should I use to enhance query speed?



    For instance: SELECT ....... ORDER BY Table1.Field1, Table2.Field2, Table3.Field3

    Conventional wisdom is that if you have ORDER BY Table1.Field1, then you need an index in Table1 that sorts Field1. If you have ORDER BY Table1.Field1 DESC, then your index needs to sort Field1 descendingly. If you have ORDER BY Table1.Field1, Table1.Field2, then you need a two-field index in Table1 that sorts Field1, Field2 -- a single-field index for Field1 won't help here (so tricky). So what should I do when ORDER BY spans multiple tables?

    Same question for WHERE clauses. If I have WHERE Table1.Field1=100 AND Table2.Field2=200 OR Table3.Field3=300, what indices would benefit the performance? Do AND and OR play a role in this?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    ?? Not sure I'm following your logic.

    If the tables are related --they have PK/FK and identified relationship(s)- your SELECT would include identified Joins. That would bring associated/related records together based on whatever sort /ORDER BY you choose.

    If the tables are not related, you could use a UNION query and Order BY whatever field(s) you feel are important to you.


    If you have a specific example in mind, then that may help readers with context.
    Last edited by orange; 03-13-2018 at 01:36 PM. Reason: spelling

  3. #3
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by orange View Post
    ?? Not sure I'm following your logic.

    If the tables are related --they have PK/FK and identified relationship(s)- your SELECT would include identified Joins. That would bring associated/related records together based on whatever sort /ORDER BY you choose.

    If the tables are not related, you could use a UNION query and Order BY whatever field(s) you feel are important to you.


    If you have a specific example in mind, then that may help readers with context.

    What if all the tables are joined by PK fields but the ORDER BY clause contains some other fields (fields from multiple tables)? Is there any indexing that would help with the query speed?

    Also, my tables have, or will soon have, millions of rows. So I need to optimize my queries in every way I can.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,946
    Fields that will commonly be used for search criteria are worth indexing, particularly if you have a large number of records.
    This will significantly reduce time needed to load the query.
    Exceptions include Boolean fields where indexing serves little or no value as you only have two possible.

    AFAIK fields listed in Order by are not important or less so in this regard
    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

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715

  6. #6
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128
    Quote Originally Posted by ridders52 View Post
    AFAIK fields listed in Order by are not important or less so in this regard
    You're right on that point. Join fields and WHERE clauses need the assist from indices, but not ORDER BY clauses. That is because ORDER BY deals fewer records, whereas the join fields and criterion fields have to pick from all records. Finally it makes sense to me.

  7. #7
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128
    Does ORDER BY actually slow down a query? I have a query that joins multiple table together to yields 800,000 rows. I don't normally create a data set this large, of course. But this is done just to see the effect of ORDER BY. And this query runs noticeably faster without ORDER BY than with it, even if the ORDER BY clause sorts an already-indexed field. I can't upload my database here since it is too big (70MB, 800k rows).

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,946
    Yes. The query does one pass to filter the records then another pass to sort them. So it takes longer

    You can simulate this in a large table by sorting by a field. It will take ages. Usually longer than filtering.
    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. Proper table properties setup
    By NightWalker in forum Database Design
    Replies: 5
    Last Post: 07-26-2016, 02:10 PM
  2. Replies: 26
    Last Post: 07-16-2015, 09:19 PM
  3. Proper use of Multiple Table design?
    By hitechartist in forum Database Design
    Replies: 2
    Last Post: 08-25-2014, 01:47 PM
  4. Indexing question
    By Helystra in forum Database Design
    Replies: 8
    Last Post: 11-22-2013, 04:24 PM
  5. Simple Question about Multiple Table Queries
    By Access_Headaches in forum Access
    Replies: 4
    Last Post: 02-13-2012, 08:36 AM

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