Using the BasicTable used earlier in this module as an example, a standard request would be to query this database by last name.
If that's the case, you will want to consider creating an index on the last name so the SQL engine will automatically keep track of the last names and be able to retrieve them quickly. Creating an index is simple and straightforward. As with the CREATE TABLE
statement from the prior lesson, you use the CREATE
statement, but with the INDEX
keyword:
This creates the index, names it
LastnameIndex
, and sets it up on BasicTable. The information in the index is created initially, then maintained automatically as rows are inserted, deleted, or
updated.
Sometimes, the syntax you use to create an index can
vary.
In some cases, you can expect an increase in the timing of your queries. It is important to place the information in your database, and then determine how you are most likely to query it. Only then can you really determine what columns need to be indexed.
Note that while indexes are helpful for performance in most cases, do not overdo it. By adding too many indexes, you can begin to impact the performance of your system negatively because the engine will be updating the indexes constantly as your data changes.