Why indexing is important for good performance (2 Viewers)

ontopofmalvern

Registered User.
Local time
Today, 15:50
Joined
Mar 24, 2017
Messages
64
Many thanks for this, I run a relatively small but complicated database with lots of tables with lots of queries and joins. Typically I need to pull out names from personel tables and join to other records. The primary key in the in personel tables will always be indexed but currently the names are not. If I am using a query with a join to populate a report should I inlcuded the indexed primary key from personel table (even though the report will typically not use it) or should I be indexing the names as well?

Currently if a report does not require the primary key I don't include it in the SELECT part of queries only in the JOIN, so I suspect I'm losing the benefit of the indexed key, is that right? (does it make sense?)
 

isladogs

MVP / VIP
Local time
Today, 15:50
Joined
Jan 14, 2017
Messages
18,186
You should index any field used regularly in search criteria as that will significantly speed up the search.
See this article Optimising Queries
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:50
Joined
Feb 19, 2013
Messages
16,553
.... and for sorting.

Currently if a report does not require the primary key I don't include it in the SELECT part of queries only in the JOIN, so I suspect I'm losing the benefit of the indexed key, is that right? (does it make sense?)
it's the join that matters in this respect, not what is displayed in a form or report. PK's are not normally displayed anyway since their value is meaningless so you don't need to select it unless you need it for some reason. You might want to index the name for sorting or criteria reasons since this is something the user would typically use to find the employee. However if the number of employees is small, you probably won't notice any real benefit.
 

Users who are viewing this thread

Top Bottom