Skip to Main Content
Learning SQL
book

Learning SQL

by Alan Beaulieu
August 2005
Beginner content levelBeginner
312 pages
8h 23m
English
O'Reilly Media, Inc.
Content preview from Learning SQL

Chapter 13. Indexes and Constraints

Because the focus of this book is on programming techniques, the first twelve chapters of this book concentrate on those elements of the SQL language used to craft powerful select, insert, update, and delete statements. There are, however, other database features that 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL

Learning SQL

Guy Vaccaro
Learning SQL

Learning SQL

Alan Beaulieu
Learning SQL

Learning SQL

Ben Forta

Publisher Resources

ISBN: 0596007272Supplemental ContentErrata Page