Table of Contents for

Learning SQL, 2nd Edition

Returning to the department table, you might decide to add an index on the name column to speed up any queries that specify a full or partial department name, as well as any update or delete operations that specify a department name. Here’s how you can add such an index to a MySQL database:

mysql> ALTER TABLE department
    -> ADD INDEX dept_name_idx (name);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

This statement creates an index (a B-tree index to be precise, but more on this shortly) on the department.name column; furthermore, the index is given the name dept_name_idx. With the index in place, the query optimizer (which we discussed in Chapter 3) can choose to use the index if it is deemed beneficial to do so (with only three rows in the department table, for example, the optimizer might very well choose to ignore the index and read the entire table). If there is more than one index on a table, the optimizer must decide which index will be the most beneficial for a particular SQL statement.

CREATE INDEX dept_name_idx
ON department (name);

All database servers allow you to look at the available indexes. MySQL users can use the show command to see all of the indexes on a specific table, as in:

mysql> SHOW INDEX FROM department \G *************************** 1. row 
*************************** 1. row ***************************
        Table: department
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: dept_id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_Comment:
*************************** 2. row ***************************
        Table: department
   Non_unique: 1
     Key_name: dept_name_idx
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_Comment:
2 rows in set (0.01 sec)

The output shows that there are two indexes on the department table: one on the dept_id column called PRIMARY, and the other on the name column called dept_name_idx. Since I have created only one index so far (dept_name_idx), you might be wondering where the other came from; when the department table was created, the create table statement included a constraint naming the dept_id column as the primary key for the table. Here’s the statement used to create the table:

CREATE TABLE department
 (dept_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT pk_department PRIMARY KEY (dept_id)  );

When the table was created, the MySQL server automatically generated an index on the primary key column, which, in this case, is dept_id, and gave the index the name PRIMARY. I cover constraints later in this chapter.

If, after creating an index, you decide that the index is not proving useful, you can remove it via the following:

mysql> ALTER TABLE department
    -> DROP INDEX dept_name_idx;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

DROP INDEX dept_name_idx; (Oracle)

DROP INDEX dept_name_idx ON department (SQL Server)

mysql> ALTER TABLE department
    -> ADD UNIQUE dept_name_idx (name);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

CREATE UNIQUE INDEX dept_name_idx
ON department (name);

mysql> INSERT INTO department (dept_id, name)
    -> VALUES (999, 'Operations');
ERROR 1062 (23000): Duplicate entry 'Operations' for key 'dept_name_idx'

mysql> ALTER TABLE employee
    -> ADD INDEX emp_names_idx (lname, fname);
Query OK, 18 rows affected (0.10 sec)
Records: 18  Duplicates: 0  Warnings: 0

Indexing is a powerful tool, but since there are many different types of data, a single indexing strategy doesn’t always do the job. The following sections illustrate the different types of indexing available from various servers.

B-tree indexes

All the indexes shown thus far are balanced-tree indexes, which are more commonly known as B-tree indexes. MySQL, Oracle Database, and SQL Server all default to B-tree indexing, so you will get a B-tree index unless you explicitly ask for another type. As you might expect, B-tree indexes are organized as trees, with one or more levels of branch nodes leading to a single level of leaf nodes. Branch nodes are used for navigating the tree, while leaf nodes hold the actual values and location information. For example, a B-tree index built on the employee.lname column might look something like Figure 13-1.

Figure 13-1. B-tree example

If you were to issue a query to retrieve all employees whose last name starts with G, the server would look at the top branch node (called the root node) and follow the link to the branch node that handles last names beginning with A through M. This branch node would, in turn, direct the server to a leaf node containing last names beginning with G through I. The server then starts reading the values in the leaf node until it encounters a value that doesn’t begin with G (which, in this case, is 'Hawthorne').

As rows are inserted, updated, and deleted from the employee table, the server will attempt to keep the tree balanced so that there aren’t far more branch/leaf nodes on one side of the root node than the other. The server can add or remove branch nodes to redistribute the values more evenly and can even add or remove an entire level of branch nodes. By keeping the tree balanced, the server is able to traverse quickly to the leaf nodes to find the desired values without having to navigate through many levels of branch nodes.

Bitmap indexes

Although B-tree indexes are great at handling columns that contain many different values, such as a customer’s first/last names, they can become unwieldy when built on a column that allows only a small number of values. For example, you may decide to generate an index on the account.product_cd column so that you can quickly retrieve all accounts of a specific type (e.g., checking, savings). Because there are only eight different products, however, and because some products are far more popular than others, it can be difficult to maintain a balanced B-tree index as the number of accounts grows.

For columns that contain only a small number of values across a large number of rows (known as low-cardinality data), a different indexing strategy is needed. To handle this situation more efficiently, Oracle Database includes bitmap indexes, which generate a bitmap for each value stored in the column. Figure 13-2 shows what a bitmap index might look like for data in the account.product_cd column.

Figure 13-2. Bitmap example

The index contains six bitmaps, one for each value in the product_cd column (two of the eight available products are not being used), and each bitmap includes a 0/1 value for each of the 24 rows in the account table. Thus, if you ask the server to retrieve all money market accounts (product_cd = 'MM'), the server simply finds all the 1 values in the MM bitmap and returns rows 7, 10, and 18. The server can also combine bitmaps if you are looking for multiple values; for example, if you want to retrieve all money market and savings accounts (product_cd = 'MM' or product_cd = 'SAV'), the server can perform an OR operation on the MM and SAV bitmaps and return rows 2, 5, 7, 9, 10, 16, and 18.

Bitmap indexes are a nice, compact indexing solution for low-cardinality data, but this indexing strategy breaks down if the number of values stored in the column climbs too high in relation to the number of rows (known as high-cardinality data), since the server would need to maintain too many bitmaps. For example, you would never build a bitmap index on your primary key column, since this represents the highest possible cardinality (a different value for every row).

Oracle users can generate bitmap indexes by simply adding the bitmap keyword to the create index statement, as in:

CREATE BITMAP INDEX acc_prod_idx ON account (product_cd);

Bitmap indexes are commonly used in data warehousing environments, where large amounts of data are generally indexed on columns containing relatively few values (e.g., sales quarters, geographic regions, products, salespeople).

Indexes are generally used by the server to quickly locate rows in a particular table, after which the server visits the associated table to extract the additional information requested by the user. Consider the following query:

mysql> SELECT emp_id, fname, lname
    -> FROM employee
    -> WHERE emp_id IN (1, 3, 9, 15);
+--------+---------+----------+
| emp_id | fname   | lname    |
+--------+---------+----------+
|      1 | Michael | Smith    |
|      3 | Robert  | Tyler    |
|      9 | Jane    | Grossman |
|     15 | Frank   | Portman  |
+--------+---------+----------+
4 rows in set (0.00 sec)

For this query, the server can use the primary key index on the emp_id column to locate employee IDs 1, 3, 9, and 15 in the employee table, and then visit each of the four rows to retrieve the first and last name columns.

If the index contains everything needed to satisfy the query, however, the server doesn’t need to visit the associated table. To illustrate, let’s look at how the query optimizer approaches the same query with different indexes in place.

The query, which aggregates account balances for specific customers, looks as follows:

mysql> SELECT cust_id, SUM(avail_balance) tot_bal
    -> FROM account
    -> WHERE cust_id IN (1, 5, 9, 11)
    -> GROUP BY cust_id;
+---------+----------+
| cust_id | tot_bal  |
+---------+----------+
|       1 |  4557.75 |
|       5 |  2237.97 |
|       9 | 10971.22 |
|      11 |  9345.55 |
+---------+----------+
4 rows in set (0.00 sec)

To see how MySQL’s query optimizer decides to execute the query, I use the explain statement to ask the server to show the execution plan for the query rather than executing the query:

mysql> EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal
    -> FROM account
    -> WHERE cust_id IN (1, 5, 9, 11)
    -> GROUP BY cust_id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: account
         type: index
possible_keys: fk_a_cust_id
          key: fk_a_cust_id
      key_len: 4
          ref: NULL
         rows: 24
        Extra: Using where
1 row in set (0.00 sec)

Without going into too much detail, here’s what the execution plan tells you:

The fk_a_cust_id index is another index generated automatically by the server, but this time it is because of a foreign key constraint rather than a primary key constraint (more on this later in the chapter). The fk_a_cust_id index is built on the account.cust_id column, so the server is using the index to locate customer IDs 1, 5, 9, and 11 in the account table and is then visiting those rows to retrieve and aggregate the available balance data.

Next, I will add a new index called acc_bal_idx on both the cust_id and avail_balance columns:

mysql> ALTER TABLE account
    -> ADD INDEX acc_bal_idx (cust_id, avail_balance);
Query OK, 24 rows affected (0.03 sec)
Records: 24  Duplicates: 0  Warnings: 0

With this index in place, let’s see how the query optimizer approaches the same query:

mysql> EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal
    -> FROM account
    -> WHERE cust_id IN (1, 5, 9, 11)
    -> GROUP BY cust_id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: account
         type: range
possible_keys: acc_bal_idx
          key: acc_bal_idx
      key_len: 4
          ref: NULL
         rows: 8
        Extra: Using where; Using index
1 row in set (0.01 sec)

Comparing the two execution plans yields the following differences:

Therefore, the server can use indexes to help locate rows in the associated table, or the server can use an index as though it were a table as long as the index contains all the columns needed by the query.

If indexes are so great, why not index everything? Well, the key to understanding why more indexes are not necessarily a good thing is to keep in mind that every index is a table (a special type of table, but still a table). Therefore, every time a row is added to or removed from a table, all indexes on that table must be modified. When a row is updated, any indexes on the column or columns that were affected need to be modified as well. Therefore, the more indexes you have, the more work the server needs to do to keep all schema objects up-to-date, which tends to slow things down.

Indexes also require disk space as well as some amount of care from your administrators, so the best strategy is to add an index when a clear need arises. If you need an index for only special purposes, such as a monthly maintenance routine, you can always add the index, run the routine, and then drop the index until you need it again. In the case of data warehouses, where indexes are crucial during business hours as users run reports and ad hoc queries but are problematic when data is being loaded into the warehouse overnight, it is a common practice to drop the indexes before data is loaded and then re-create them before the warehouse opens for business.

In general, you should strive to have neither too many indexes nor too few. If you aren’t sure how many indexes you should have, you can use this strategy as a default:

After you have built your initial set of indexes, try to capture actual queries against your tables, and modify your indexing strategy to fit the most-common access paths.

Constraints are generally created at the same time as the associated table via the create table statement. To illustrate, here’s an example from the schema generation script for this book’s example database:

CREATE TABLE product
 (product_cd VARCHAR(10) NOT NULL,
  name VARCHAR(50) NOT NULL,
  product_type_cd VARCHAR (10) NOT NULL,
  date_offered DATE,
  date_retired DATE,
      CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
        REFERENCES product_type (product_type_cd),
      CONSTRAINT pk_product PRIMARY KEY (product_cd)
 );

The product table includes two constraints: one to specify that the product_cd column serves as the primary key for the table, and another to specify that the product_type_cd column serves as a foreign key to the product_type table. Alternatively, you can create the product table without constraints, and add the primary and foreign key constraints later via alter table statements:

ALTER TABLE product
ADD CONSTRAINT pk_product PRIMARY KEY (product_cd);

ALTER TABLE product
ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
  REFERENCES product_type (product_type_cd);

If you want to remove a primary or foreign key constraint, you can use the alter table statement again, except that you specify drop instead of add, as in:

ALTER TABLE product
DROP PRIMARY KEY;

ALTER TABLE product
DROP FOREIGN KEY fk_product_type_cd;

While it is unusual to drop a primary key constraint, foreign key constraints are sometimes dropped during certain maintenance operations and then reestablished.

As you saw earlier in the chapter, constraint creation sometimes involves the automatic generation of an index. However, database servers behave differently regarding the relationship between constraints and indexes. Table 13-1 shows how MySQL, SQL Server, and Oracle Database handle the relationship between constraints and indexes.

MySQL, therefore, generates a new index to enforce primary key, foreign key, and unique constraints, SQL Server generates a new index for primary key and unique constraints but not for foreign key constraints, and Oracle Database takes the same approach as SQL Server except that Oracle will use an existing index (if an appropriate one exists) to enforce primary key and unique constraints. Although neither SQL Server nor Oracle Database generates an index for a foreign key constraint, both servers’ documentation advises that indexes be created for every foreign key.

With foreign key constraints in place, if a user attempts to insert a new row or change an existing row such that a foreign key column doesn’t have a matching value in the parent table, the server raises an error. To illustrate, here’s a look at the data in the product and product_type tables:

mysql> SELECT product_type_cd, name
    -> FROM product_type;
+-----------------+-------------------------------+
| product_type_cd | name                          |
+-----------------+-------------------------------+
| ACCOUNT         | Customer Accounts             |
| INSURANCE       | Insurance Offerings           |
| LOAN            | Individual and Business Loans |
+-----------------+-------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT product_type_cd, product_cd, name
    -> FROM product
    -> ORDER BY product_type_cd;
+-----------------+------------+-------------------------+
| product_type_cd | product_cd | name                    |
+-----------------+------------+-------------------------+
| ACCOUNT         | CD         | certificate of deposit  |
| ACCOUNT         | CHK        | checking account        |
| ACCOUNT         | MM         | money market account    |
| ACCOUNT         | SAV        | savings account         |
| LOAN            | AUT        | auto loan               |
| LOAN            | BUS        | business line of credit |
| LOAN            | MRT        | home mortgage           |
| LOAN            | SBL        | small business loan     |
+-----------------+------------+-------------------------+
8 rows in set (0.01 sec)

There are three different values for the product_type_cd column in the product_type table (ACCOUNT, INSURANCE, and LOAN). Of the three values, two of them (ACCOUNT and LOAN) are referenced in the product table’s product_type_cd column.

The following statement attempts to change the product_type_cd column in the product table to a value that doesn’t exist in the product_type table:

mysql> UPDATE product
    -> SET product_type_cd = 'XYZ'
    -> WHERE product_type_cd = 'LOAN';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails ('bank'.'product', CONSTRAINT 'fk_product_type_cd' FOREIGN KEY
('product_type_cd') REFERENCES 'product_type' ('product_type_cd'))

Because of the foreign key constraint on the product.product_type_cd column, the server does not allow the update to succeed, since there is no row in the product_type table with a value of XYZ in the product_type_cd column. Thus, the foreign key constraint doesn’t let you change a child row if there is no corresponding value in the parent.

What would happen, however, if you tried to change the parent row in the product_type table to XYZ? Here’s an update statement that attempts to change the LOAN product type to XYZ:

mysql> UPDATE product_type
    -> SET product_type_cd = 'XYZ'
    -> WHERE product_type_cd = 'LOAN';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails ('bank'.'product', CONSTRAINT 'fk_product_type_cd' FOREIGN KEY
('product_type_cd') REFERENCES 'product_type' ('product_type_cd'))

Once again, an error is raised; this time because there are child rows in the product table whose product_type_cd column contains the value LOAN. This is the default behavior for foreign key constraints, but it is not the only possible behavior; instead, you can instruct the server to propagate the change to all child rows for you, thus preserving the integrity of the data. Known as a cascading update, this variation of the foreign key constraint can be installed by removing the existing foreign key and adding a new one that includes the on update cascade clause:

mysql> ALTER TABLE product
    -> DROP FOREIGN KEY fk_product_type_cd;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE product
    -> ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
    ->   REFERENCES product_type (product_type_cd)
    ->   ON UPDATE CASCADE;
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

With this modified constraint in place, let’s see what happens when the previous update statement is attempted again:

mysql> UPDATE product_type
    -> SET product_type_cd = 'XYZ'
    -> WHERE product_type_cd = 'LOAN';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

This time, the statement succeeds. To verify that the change was propagated to the product table, here’s another look at the data in both tables:

mysql> SELECT product_type_cd, name
    -> FROM product_type;
+-----------------+-------------------------------+
| product_type_cd | name                          |
+-----------------+-------------------------------+
| ACCOUNT         | Customer Accounts             |
| INSURANCE       | Insurance Offerings           |
| XYZ             | Individual and Business Loans |
+-----------------+-------------------------------+
3 rows in set (0.02 sec)

mysql> SELECT product_type_cd, product_cd, name
    -> FROM product
    -> ORDER BY product_type_cd;
+-----------------+------------+-------------------------+
| product_type_cd | product_cd | name                    |
+-----------------+------------+-------------------------+
| ACCOUNT         | CD         | certificate of deposit  |
| ACCOUNT         | CHK        | checking account        |
| ACCOUNT         | MM         | money market account    |
| ACCOUNT         | SAV        | savings account         |
| XYZ             | AUT        | auto loan               |
| XYZ             | BUS        | business line of credit |
| XYZ             | MRT        | home mortgage           |
| XYZ             | SBL        | small business loan     |
+-----------------+------------+-------------------------+
8 rows in set (0.01 sec)

As you can see, the change to the product_type table has been propagated to the product table as well. Along with cascading updates, you can specify cascading deletes as well. A cascading delete removes rows from the child table when a row is deleted from the parent table. To specify cascading deletes, use the on delete cascade clause, as in:

ALTER TABLE product
ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)
  REFERENCES product_type (product_type_cd)
  ON UPDATE CASCADE
  ON DELETE CASCADE;

With this version of the constraint in place, the server will now update child rows in the product table when a row in the product_type table is updated, as well as delete child rows in the product table when a row in the product_type table is deleted.

Cascading constraints are one case in which constraints do directly affect the code that you write. You need to know which constraints in your database specify cascading updates and/or deletes so that you know the full effect of your update and delete statements.

Modify the account table so that customers may not have more than one account for each product.

Generate a multicolumn index on the transaction table that could be used by both of the following queries:

SELECT txn_date, account_id, txn_type_cd, amount
FROM transaction
WHERE txn_date > cast('2008-12-31 23:59:59' as datetime);

SELECT txn_date, account_id, txn_type_cd, amount
FROM transaction
WHERE txn_date > cast('2008-12-31 23:59:59' as datetime)
  AND amount < 1000;