Using database indexes to boost search performance

In Chapter 4, you've built a search engine capable of searching through the list of accounts in the Accounts table. When the list of accounts increases into the hundreds or thousands, you will find that the time it takes to perform a search starts to increase. It is not unheard of to have users wait 10 seconds or more for a search to complete. This is, of course, unacceptable in cases where the application is time critical.

You can easily boost search performance without changing a single line of code just by creating an index on regularly searched columns in the database. Indexes allow the database engine to build an internal "map" of your data that allows the database to zoom and narrow down on the exact desired data. Without an index, the database would have to do a full table scan—reading each table row by row to find the matching record.

For example, consider the following SQL statement. Without an index, the database engine would have to scan the entire Accounts table row by row to check if the FirstName matches "John."

SELECT * FROM Accounts WHERE FirstName = 'John'

If you create an index on the FirstName column in the Accounts table, any and all SQL statements that filter data based on the FirstName column will experience a large performance increment. You can create an index in SQL Server CE using the following DDL statement:

CREATE INDEX FirstNameIdx ON Accounts(FirstName ASC)

For the Oracle Lite database, you can create an index using the following DDL:

CREATE INDEX FirstNameIdx ON Accounts(FirstName)

Run these DDL statements using the Query Analyzer or MSql tools provided by SQL Server CE and Oracle Lite respectively. You can also create indexes on multiple columns at the same time. For example, if you know that 80 percent of your users will search using both the FirstName and the AccountType columns, you can create a single index that maps to both of these columns. The following DDL shows how this can be done on SQL Server CE:

CREATE INDEX FirstName_TypeIdx ON Accounts(FirstName ASC,

AccountType ASC)

The following DDL shows how this can be done on Oracle Lite.

CREATE INDEX FirstName_TypeIdx ON Accounts(FirstName, AccountType)

Multi-column indexes such as the one we just saw would yield the most optimal performance if users specify both the first name and account type in the search. If the user specifies only the first name or only the account type, the index will not be optimally utilized, and this will lead to little or no performance increment.

Too many indexes

Take note that when you create an index on a table, the performance of any SQL INSERT or UPDATE done on that table decreases slightly — this is because the database engine has to now store additional index data for each record written in the database. If you create too many indexes, the INSERT and UPDATE performance decrement may build up undesirably. You should, therefore, build indexes only on columns or column groups that are anticipated to be frequently used in a search. For example, if you know that most of your users will run a search using the first name of the account rather than the last name, create an index only on the FirstName column.

Was this article helpful?

0 0
SEO Basics Tips For Small Business Owners

SEO Basics Tips For Small Business Owners

Whether you've created a website already or are planning to create one in the near future, you know that's only half the battle. The other half is getting your website found in the search engines in hopes of attracting those interested in the type of products or services you offer.

Get My Free Ebook

Post a comment