Revision as of 08:44, 7 March 2014

CREATE INDEX

You can create an index on a table to improve the performance of certain queries.

• There is a small performance cost in creating an index. Generally your SELECT queries will run faster but your INSERT, UPDATE and DELETE commands will run slower.
• This is usually a price worth paying because the cost is very small while the improvement is dramatic.
• On the other hand - don't go adding indexes unless you need them.
• All tables should have a PRIMARY KEY which will automatically get an index - there is no point in creating an index that is the same as the primary key.

The table games shows the year and the city hosting the Olympic Games.

 DROP TABLE games

 CREATE TABLE games(
  yr INTEGER PRIMARY KEY,
  city VARCHAR(20));
INSERT INTO games VALUES (2000,'Sydney');
INSERT INTO games VALUES (2004,'Athens');
INSERT INTO games VALUES (2008,'Beijing');
INSERT INTO games VALUES (2012,'London');

CREATE INDEX gamesIdx ON games(city,yr);
SELECT yr, city FROM games

See also

• SELECT Tutorial
• SELECT WHERE