Difference between revisions of "CREATE INDEX"
From SQLZOO
| Line 1: | Line 1: | ||
| − | |||
<h1>CREATE INDEX</h1> | <h1>CREATE INDEX</h1> | ||
| + | <p>You can create an index on a table to improve the performance of certain queries.</p> | ||
| + | <ul> | ||
| + | <li>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.</li> | ||
| + | <ul> | ||
| + | <li>This is usually a price worth paying because the cost is very small while the improvement is dramatic.</li> | ||
| + | <li>On the other hand - don't go adding indexes unless you need them.</li> | ||
| + | </ul> | ||
| + | <li>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.</li> | ||
| + | </ul> | ||
<p>The table <code>games</code> shows the year and the city hosting the Olympic Games.</p> | <p>The table <code>games</code> shows the year and the city hosting the Olympic Games.</p> | ||
<table> | <table> | ||
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.
| 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
