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:
KEYnamecity
(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: ...
Get Web Database Applications with PHP, and MySQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.