Database Reference
In-Depth Information
ref: NULL
rows: 4
Extra: Using where
The
EXPLAIN
statement here analyzes the
SELECT
statement given, which is selecting
all of the columns in the
humans
table where the value for the
name_last
column
equals
Hollar
. What is of interest to us in the results is the
possible_keys
field and
the
key
field — a key is the column on which a table is indexed. However, the wordskey
andindexare fairly interchangeable. The
possible_keys
field would show the keys
that the
SELECT
statement could have used. In this case, there is no index related to the
name_last
column. The
key
would list the index that the statement actually used.
Again, in this case there were none, so it shows a value of NULL. There are only four
names in this table, so an index would not make a noticeable difference in performance.
However, if this table might one day have thousands of names, an index will greatly im-
prove the performance of look-ups on people's names.
In addition to sometimes searching the
humans
table based on the member's last name,
suppose that users sometimes search based on the first name, and sometimes based on
both the first and last names. To prepare for those possibilities and to improve perform-
ance for a time when the table will have many records, let's create an index that combines
the two columns. To do this, we will usethe
ALTER TABLE
statement with the
ADD
INDEX
clause like so:
ALTER TABLE
birdwatchers
.
humans
ADD INDEX
human_names
(
name_last
,
name_first
);
Now let's run the
SHOW CREATE TABLE
statement to see how the index looks from
that perspective:
SHOW CREATE TABLE birdwatchers.humans \G
*************************** 1. row ***************************
Table: humans
Create Table: CREATE TABLE `humans` (
`human_id` int(11) NOT NULL AUTO_INCREMENT,
`formal_title` varchar(25) COLLATE latin1_bin DEFAULT NULL,
`name_first` varchar(25) COLLATE latin1_bin DEFAULT NULL,
`name_last` varchar(25) COLLATE latin1_bin DEFAULT NULL,
`email_address` varchar(255) COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (`human_id`),
KEY `human_names` (`name_last`,`name_first`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin
