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
discussed earlier in our introduction to SQL, each table should have
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:
After you define this index, some queries that select a particular
customer through a
WHERE clause can use it. Consider an example: ...