Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
    • What Is an Index?
    • How Do Indexes Work?
    • The CREATE INDEX Command
    • Types of Indexes
    • When Should Indexes Be Considered?
    • When Should Indexes Be Avoided?
    • Summary
    • Q&A
    • Workshop
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

Types of Indexes

There are different types of indexes that can be created on tables in a database, all of which serve the same goal—to improve database performance by expediting data retrieval. This hour discusses single-column indexes, composite indexes, and unique indexes.

Single-Column Indexes

Indexing on a single column of a table is the simplest and most common manifestation of an index. Obviously, a single-column index is one that is created based on only one table column. The basic syntax is as follows:

   
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)

For example, if you want to create an index on the EMPLOYEE_TBL table for employees' last names, the command used to create the index would look like the following:

CREATE INDEX NAME_IDX 
ON EMPLOYEE_TBL (LAST_NAME);

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. Otherwise, the unique index performs the same way a regular index performs. The syntax is as follows:

   
CREATE UNIQUE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)

If you want to create a unique index on the EMPLOYEE_TBL table for an employee's last name, the command used to create the unique index would look like the following:

CREATE UNIQUE INDEX NAME_IDX 
ON EMPLOYEE_TBL (LAST_NAME);

The only problem with this index is that every individual's last name in the EMPLOYEE_TBL table must be unique—pretty impractical. However, a unique index should be created for a column, such as an individual's Social Security number, because each of these numbers for each individual is unique.

You may be wondering, "What if an employee's SSN were the primary key for a table?" An index is usually implicitly created when you define a primary key for a table. However, a company can use a fictitious number for an employee ID, but maintain each employees' SSN for tax purposes. You probably want to index this column and ensure that all entries into this column are unique values.

Composite Indexes

A composite index is an index on two or more columns of a table. You should consider performance when creating a composite index because the order of columns in the index has a measurable effect on data retrieval speed. Generally, the most restrictive value should be placed first for optimum performance. However, the columns that will always be specified should be placed first. The syntax is as follows:

   
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN1, COLUMN2)

An example of a composite index follows:

CREATE INDEX ORD_IDX 
ON ORDERS_TBL (CUST_ID, PROD_ID);

In this example, you create a composite index based on two columns in the ORDERS_TBL table: CUST_ID and PROD_ID. You assume that these two columns are frequently used together as conditions in the WHERE clause of a query.

Single-Column Versus Composite Indexes

In deciding whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions. Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, a composite index would be the best choice.

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. Why are indexes automatically created for these constraints? Imagine that you are the database server. A user adds a new product to the database. The product identification is the primary key on the table, which means that it must be a unique value. To efficiently check to make sure the new value is unique among hundreds or thousands of records, the product identifications in the table must be indexed. Therefore, when you create a primary key or unique constraint, an index is automatically created for you.

Share This