Database Reference
In-Depth Information
CREATE INDEX
The above is an extremely simple explanation of an index, and there are many types of
indexes. If you want to understand these more then you should obtain one of the many
database theory topics that are available. However, if you want to quickly add an index to a
table, you can do it without much thought by using the following:
CREATE INDEX indexname
ON tablename (columnname)
If you are creating an index on a PRIMARY KEY column, where all of the data in the col-
umn will be unique, you can create a unique index as follows:
CREATE UNIQUE INDEX indexname
ON tablename (columnname)
You can also use the ALTER command to add an index to a table as follows:
ALTER TABLE tablename
ADD INDEX indexname (columnname)
On all three of the scripts above, the
columnname
can actually refer to one or more
columns, separated by commas, as you can have an index that refers to multiple columns.
One of the EXPLAIN commands that we issued earlier showed that the join command
would not use a primary key as a possible key for the join on the log table. This would mean
that we could speed the query up by using an index on the log table. If we will be running
this query often, we can optimize it by adding the index. The query will be using the
web-
pageid
column to join, so we will add an index to the table as follows:
ALTER TABLE log
ADD INDEX logindex (webpageid)
We need to EXPLAIN the query again to see if the new index has made any difference to
the query, so run it again as follows:
EXPLAIN SELECT
log.id as logid,
webpage.title AS pagetitle,
log.browser,
log.datecreated AS logdate,
log.ipnumber,
log.referringpage,
cookies.datecreated AS cookiecreated
FROM
log, webpage, cookies
WHERE
webpage.id = log.webpageid
AND cookies.cookieid=log.cookieid
Search WWH ::
Custom Search