Difference between revisions of "CREATE INDEX"

From SQLZOO
Jump to: navigation, search
(Created page with " <h1>CREATE INDEX</h1> <p>The table <code>games</code> shows the year and the city hosting the Olympic Games.</p> <table> <caption align='center'>games</caption> <tr><th align...")
 
 
(2 intermediate revisions by one user not shown)
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>
 +
*You can create an index that include one or more columns.
 +
*There is a small performance cost in creating an index.
 +
**Your [[SELECT]] queries will run dramatically faster.
 +
**Your [[INSERT]], [[UPDATE]] and [[DELETE]] commands will run slightly slower.
 +
*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
 
<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>
Line 23: Line 31:
 
When you create an index you make it faster to find a particular row.  
 
When you create an index you make it faster to find a particular row.  
 
You can also make the JOIN operator faster.   
 
You can also make the JOIN operator faster.   
<source lang='sql' class='def e-oracle'>
 
CREATE INDEX gamesIdx ON games(city,yr);
 
SELECT yr, city FROM scott.games
 
</source>
 
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
CREATE INDEX gamesIdx ON games(city,yr);
 
CREATE INDEX gamesIdx ON games(city,yr);

Latest revision as of 09:50, 7 March 2014

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.

games
yrcity
2000Sydney
2004Athens
2008Beijing
2012London
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

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense