Web Database Applications with PHP, and MySQL

More on SQL and MySQL

In this section we discuss miscellaneous tools and techniques for using SQL and MySQL. We introduce:

• Choosing keys and indexes for fast searching

• Elementary database-tuning techniques

• Adding and deleting users of a DBMS, and changing user permissions

• Limitations of MySQL

Keys, Primary Keys, and Indexes

As discussed earlier in our introduction to SQL, each table should have a PRIMARY KEY definition as part of the CREATE TABLE statement. A primary key is an attribute—or set of attributes—that uniquely identifies a row in a table. Storing two rows with the same primary key isn’t permitted and, indeed, an attempt to INSERT duplicate primary keys produces an error.

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: ...