3.10.1. Keys, Primary Keys, and Indexes

In MySQL, the attribute values of the primary key are stored in an index to allow fast access to a row. The default MySQL index type is fast for queries that find a specific row, a range of rows, for joins between tables, grouping data, ordering data, and finding minimum and maximum values. Indexes don't provide any speed improvement for retrieving all the rows in a table or for other query types.

Indexes are also useful for fast access to rows by values other than those that are associated with attributes in the primary key. For example, in the customer table, you might define an index by adding the clause:

KEY namecity (surname,firstname,city) 

to the CREATE TABLE statement. After you define this index, some queries that select a particular customer through a WHERE clause can use it. Consider an example:

SELECT * FROM customer
  WHERE surname = 'Marzalla' AND
  firstname = 'Dimitria' AND
  city = 'St Albans';

This query can use the new index to locate—in at most a few disk accesses—the row that matches the search criteria. Without the index, the DBMS must scan all the rows in the customer table and compare each row to the WHERE clause. This might be quite slow and certainly requires significantly more disk accesses than the index-based approach (assuming the table has more than a few rows).

A particular feature of DBMSs is that they develop a query evaluation strategy and optimize it without any interaction from the user or programmer. If an index is available, and it makes sense to use it in the context of a query, the DBMS does this automatically. All you need to do is identify which queries are common, and make an index available for those common queries by adding the KEY clause to the CREATE TABLE statement or using ALTER TABLE on an existing table.

Careful index design is important. The namecity index we have defined can also speed queries other than those that supply a complete surname, firstname, and city. For example, consider a query:

SELECT * FROM customer
  WHERE surname = 'LaTrobe' AND
  firstname = 'Anthony';

This query can also use the index namecity, because the index permits access to rows in sorted order first by surname, then firstname, and then city. With this sorting, all "LaTrobe, Anthony" index entries are clustered together in the index. Indeed, the index can also be used for the query:

SELECT * FROM customer
  WHERE surname LIKE 'Mar%';

Again, all surnames beginning with "Mar" are clustered together in the index. However, the index can't be used for a query such as:

SELECT * FROM customer
  WHERE firstname = 'Dimitria' AND
  city = 'St Albans';

The index can't be used because the leftmost attribute named in the index, surname, isn't part of the WHERE clause. In this case, all rows in the customer table must be scanned and the query is much slower (again assuming there are more than a few rows in the customer table, and assuming there is no other index).

There are other cases in which an index can't be used, such as when a query contains an OR that isn't on an indexed attribute:

SELECT * FROM customer
  WHERE surname = 'Marzalla' OR
  email = '[email protected]';

Again, the customer table must be completely scanned, because the second condition, email='[email protected]', requires all rows to be retrieved as there is no index available on the attribute email. Also, the case where the OR ed attribute isn't the leftmost attribute in an index requires a complete scan of the customer table. The following example requires a complete scan:

SELECT * FROM customer
WHERE firstname = 'Dimitria' OR
surname = 'Marzalla';

If all the attributes in the index are used in all the queries, to optimize index size, the leftmost attribute in the KEY clause should be the attribute with the highest number of duplicate entries.

Because indexes speed up queries, why not create indexes on all the attributes you can possibly search on? The answer is that while indexes are fast for searching, they consume space and require updates each time rows are added or deleted, or key attributes are changed. So, if a database is largely static, additional indexes have low overheads, but if a database changes frequently, each additional index slows the update process significantly. In either case, indexes consume additional space, and unnecessary indexes should be avoided.

KEY namecity (surname(10),firstname(3),city(2));

This uses only the first 10 characters of surname, 3 of firstname, and the first 2 characters of city to distinguish index entries. This is quite reasonable, because 10 characters from a surname distinguishes between most surnames, and the addition of a few characters from a first name and the prefix of their city should be sufficient to uniquely identify almost all customers. Having a smaller index with less information can also mean that queries are actually faster, because more index information can be retrieved from disk per second, and disk retrieval speed is almost always the bottleneck in query performance.

3.10.2. Tuning the Database System

Here are some ways to improve DBMS performance:

3.10.3. Adding and Deleting Users

A user, hugh, who has full control over all aspects of the DBMS and can access the DBMS from the machine that hosts the DBMS, can be created with the statement:

GRANT ALL PRIVILEGES ON *.* TO hugh@localhost
  IDENTIFIED BY 'password' WITH GRANT OPTION;

Allowing access over a network can be added with:

GRANT ALL PRIVILEGES ON *.* TO hugh@"%" 
  IDENTIFIED BY 'password' WITH GRANT OPTION;

There is no need to allow network access for a web database application if the middle-tier components—the web server and scripting engine—are installed on the same machine as the DBMS.

This user can then connect to the database from the shell with the command:

% mysql -ppassword -uhugh

The user information is stored in the mysql database in the user table, which can be explored with:

USE mysql;
SELECT * FROM user;

The mysql database and the user table can be managed in the same way as any other database. For example, you can update the password of the new user with the UPDATE statement:

UPDATE user 
  SET password=password('newpwd') 
  WHERE user='hugh';

Note the use of the password( ) function we described earlier to encrypt the password for storage in the user table.

3.10.4. Limitations of MySQL

SELECT DISTINCT wine_id FROM wine
WHERE wine_id IN 
  (SELECT wine_id from inventory);

The query returns the wine_id values from the wine table that are found in the inventory table. Nested queries use the IN, NOT IN, EXISTS, and NOT EXISTS operators.

In many cases, a nested query can be rewritten as a join query. For example, to find the wines that are in stock, you can use the following join query:

SELECT DISTINCT wine.wine_id FROM wine, inventory
WHERE wine.wine_id = inventory.wine_id;

However, some nested queries can't be rewritten as join queries; for difficult queries, temporary tables are often a useful workaround.

To avoid UPDATE and DELETE problems, consider adding additional attributes to tables at design time. For example, in the winestore we added a DATE attribute to the items table so that shopping-cart items can be removed easily if they aren't purchased within one day. Removing rows from the items table based on the DATE in the orders table is difficult without support for nested queries.