Indexing
While MySQL has better performance than any of the larger database servers, some problems still call for careful database design. For instance, if we had a table with millions of rows of data, a search for a specific row would take a long time. Most database engines allow indexes to aid in such searches.
Indexes help the database store data in a way that makes for quicker searches. Unfortunately, you sacrifice disk space and modification speed for the benefit of quicker searches. The most efficient use of indexes is to create an index for columns on which you tend to search the most. MySQL supports the following syntax for creating an index for a table:
CREATE INDEXindex_name
ONtablename
(column1
,column2
,...
,columnN
)
MySQL also lets you create an index at the same time you create a table using the following syntax:
CREATE TABLE material (id INT NOT NULL, name CHAR(50) NOT NULL, resistance INT, melting_pt REAL, INDEX index1 (id, name), UNIQUE INDEX index2 (name))
The previous example creates two indexes for the table. The first
index—named index1
—consists of both
the id
and name
fields. The
second index includes only the name
field and
specifies that values for the name
field must
always be unique. If you try to insert a field with a
name
held by a row already in the database, the
insert will fail. Generally, you should declare all fields in a
unique index as NOT NULL
.
Even though we created an index for name
by
itself, we did not create an index for just id
. If we ...
Get Managing & Using MySQL, 2nd Edition 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.