Learning SQL, 3rd Edition

Chapter 13. Indexes and Constraints

Because the focus of this book is on programming techniques, the first 12 chapters concentrated on elements of the SQL language that you can use to craft powerful select, insert, update, and delete statements. However, other database features indirectly affect the code you write. This chapter focuses on two of those features: indexes and constraints.

Indexes

When you insert a row into a table, the database server does not attempt to put the data in any particular location within the table. For example, if you add a row to the customer table, the server doesn’t place the row in numeric order via the customer_id column or in alphabetical order via the last_name column. Instead, the server simply places the data in the next available location within the file (the server maintains a list of free space for each table). When you query the customer table, therefore, the server will need to inspect every row of the table to answer the query. For example, let’s say that you issue the following query:

mysql> SELECT first_name, last_name
    -> FROM customer
    -> WHERE last_name LIKE 'Y%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LUIS       | YANEZ     |
| MARVIN     | YEE       |
| CYNTHIA    | YOUNG     |
+------------+-----------+
3 rows in set (0.09 sec)

To find all customers whose last name begins with Y, the server must visit each row in the customer table and inspect the contents of the last_name