CREATE INDEX
From SQLZoo
CREATE INDEX
You can create an index on a table to improve the performance of certain queries.
- You can create an index that include one or more columns.
- There is a small performance cost in creating an index.
- All tables should have a PRIMARY KEY. This will automatically get an index
- There is no point in creating an index that is the same as the primary key.
- A typical SELECT .. JOIN query involves a FOREIGN KEY in one table and a PRIMARY KEY in another.
- You should create an index on the FOREIGN KEY to make the join run faster
The table games
shows the year and the city hosting the Olympic Games.
yr | city |
---|---|
2000 | Sydney |
2004 | Athens |
2008 | Beijing |
2012 | London |
schema:scott
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');
When you create an index you make it faster to find a particular row. You can also make the JOIN operator faster.
CREATE INDEX gamesIdx ON games(city,yr);
SELECT yr, city FROM games
See also