or 02-9262-7770 
| Introduction to Database Indexes | |
By Chris Smith |
Published
02/15/2006
|
Programming
|
Rating:
|
|
|
Multi-Table QueriesAnother reason for creating indexes is for tables that reference other tables. For example, in a CMS you might have a news table that looks something like this: CREATE TABLE newsitem ( newsid INT PRIMARY KEY, newstitle VARCHAR(255), newscontent TEXT, authorid INT, newsdate TIMESTAMP ); and another table for authors: CREATE TABLE authors ( authorid INT PRIMARY KEY, username VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255) ); A query like this: SELECT newstitle, firstname, lastname FROM newsitem n, authors a WHERE n.authorid=a.authorid; ... will be take advantage of an index on the newsitem authorid: CREATE INDEX newsitem_authorid ON newsitem(authorid); This allows the database to very quickly match the records from the 'newsitem' table to the 'authors' table. In database terminology this is called a table join - you should index any fields involved in a table join like this. Since the 'authorid' in the authors table is a primary key, it is already indexed. The same goes for the 'newsid' in the news table, so we don't need to look at those cases. On a side note, table aliases make things a lot easier to see what's happening. Using 'newsitem n' and 'authors a' means we don't have to write: SELECT newstitle, firstname, lastname FROM newsitem, authors WHERE newsitem.authorid=authors.authorid; for more complicated queries where more tables are referenced this can be extremely helpful and make things really easy to follow. In a more complicated example, a news item could exist in multiple categories, so in a design like this: CREATE TABLE newsitem ( newsid INT PRIMARY KEY, newstitle VARCHAR(255), newscontent TEXT, authorid INT, newsdate TIMESTAMP ); CREATE TABLE newsitem_categories ( newsid INT, categoryid INT ); CREATE TABLE categories ( categoryid INT PRIMARY KEY, categoryname VARCHAR(255) ); This query: SELECT n.newstitle, c.categoryname FROM categories c, newsitem_categories nc, newsitem n WHERE c.categoryid=nc.categoryid AND nc.newsid=n.newsid; ... will show all category names and newstitles for each category. To make this particular query fast we need to check we have an index on: newsitem newsid newsitem_categories newsid newsitem_categories categoryid categories categoryid Note: Because the newsitem newsid and the categories categoryid fields are primary keys, they already have indexes. We need to check there are indexes on the "join" table - newsitem_categories This will do it: CREATE INDEX newscat_news ON newsitem_categories(newsid); CREATE INDEX newscat_cats ON newsitem_categories(categoryid); |
|
