Learning SQL, 2nd 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 department table, the server doesn’t place the row in numeric order via the dept_id column or in alphabetical order via the 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 department 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 dept_id, name
    -> FROM department
    -> WHERE name LIKE 'A%';
+---------+----------------+
| dept_id | name           |
+---------+----------------+
|       3 | Administration |
+---------+----------------+
1 row in set (0.03 sec)

To find all departments whose name begins with A, the server must visit each row in the department table and inspect the contents of the name column; if the department name begins with A, then the row is added to the result set. This type of access ...