The QuickBooks database is comprised of tables, each with columns, rows and fields of data. QuickBooks tables are the critical components of the QuickBooks database that store and relate data. In databases, such as QuickBooks, an index is one way of providing quicker access to data.
An "Index" is an important part of the QuickBooks database, even though indexes are not specific pieces of data that Users create. Indexes (indices) are pointers used to provide rapid, random access to data within a table, and can be created on any combination of attributes based on the relationships that exist between the various QuickBooks tables, or even components of those tables.
When a User executes a query, it is processed by the QuickBooks Database Server using indexes to find matching data randomly using the index, without having to verify each data element in turn, thus permitting the queries to find the data quickly. This is analogous to using the index of a book to go directly to the page on which the information you want to see is found, (i.e. you do not have to read the entire book to find what you are looking for.)
Relational databases typically use multiple indexing techniques, each of which is optimized for some combination of data distribution, relation size, and typical access pattern. QuickBooks create these indexes on one or more tables (or table components) during routine use. QuickBooks indices are one potential source of database corruption.
Indexes radically increase the performance of the QuickBooks application, saving time to execute queries including not only reports, or ‘finds/searches’, but also when processing transactions that will link to other transactions (for example, when processing a Vendor bill that will link to an open Purchase Order).
The QuickBooks database can contain thousands or millions of rows of data, and if the ‘backend’ of the QuickBooks application did not create indexes then it would be to slow to find data, or build reports. Without indexes, the QuickBooks Database Server would have to visit every row in every table which would not only be a lengthy process, but a waste of computer resources.
When an index is created within a database, an actual physical object is being saved to disk. A ‘Primary Key’ is a logical object that defines a set of properties on one, or a set of columns, to require that the columns are unique and not null.
In QuickBooks the ‘source’ transaction is known as the Master record. All related transactions linked to that Master are the Target records. For example, the ‘header’ of most transactions in QuickBooks represents the source transactions and the details of a transaction (what you might think of as the stub, in the case of a check) are the target transactions. For more on this concept see my TECHNO TOPIC article titled QuickBooks Database Concepts (Part 1)
QuickBooks creates an ‘index’ for every ‘primary key’, and the ‘target’ ID and ‘transaction’ ID make up that primary key. Because these values are unique and not null, they can be used to identify a single row in a specific table every time.
Indexes can be one type of database corruption in QuickBooks, There can be many causes of index corruption including, Unexpected Application Shutdown, Virus or Trojan Infection, Power Supply Issues (Blackout, brownout, etc.), and Bad Sectors on the Hard Drive(s).
One Index related corruption error you might se in the QBWin.log file by QuickBooks is:
Error: Verify _______ List: Index out of range; index _______ is greater than _______
(Obviously the blanks in the above 'error example' would be filled with specific information about the missing data. I prefer to insert blanks rather than using 'squiggles' like many formal computer database textbooks.) This type of index error can occur when the Verify utility encounters a target that is outside the expected range. After the verify process is fails to complete, the message “Data lost integrity” will usually be displayed.
One example of how QuickBooks uses index, is the Search feature that was first released in 2011 versions of QuickBooks. Because this process essentially creates a special set of indexes for every field of data within the database it enables you to find information down to the smallest detail. If you add a ‘notation’ within the description field of a transaction, even in a description field not associated with a ‘item’ or ‘account’ you can still find the transaction containing that note relatively quickly. That is the ‘power’ of indexing. Of course building such an index structure can consume computer resources and slow down other functions; this is why it is best to limit the frequency for re-indexing the data. (Note: if you don’t routinely use this Search feature, I suggest that you turn off the option for re-indexing, you will find it under ‘search’ preferences.)
Without indexes, finding a check previously written in QuickBooks would be like searching through office after office to find out whose desk the pile of papers containing the photo-copy of the check was sitting on. While users sometimes complain about the speed with which QuickBooks can locate information (when it takes more than a few seconds), without Indexes users would be ‘dead in the water’.
Indexes make the lives of QuickBooks users speedy in comparison to not having this process working in the background. So the next time you look for something on your desk, try it ‘blind folded’ and then you will understand what QuickBooks would be like without ‘indexes’.