or 02-9262-7770 
| Introduction to Database Indexes | |
By Chris Smith |
Published
02/15/2006
|
Programming
|
Rating:
|
|
|
Introduction to Database IndexesPut simply, database indexes help speed up retrieval of data. The other great benefit of indexes is that your server doesn't have to work as hard to get the data. They are much the same as book indexes, providing the database with quick jump points on where to find the full reference (or to find the database row). There are both advantages and disadvantages to using indexes,however. One disadvantage is they can take up quite a bit of space - check a textbook or reference guide and you'll see it takes quite a few pages to include those page references. Another disadvantage is using too many indexes can actually slow your database down. Thinking of a book again, imagine if every "the", "and" or "at" was included in the index. That would stop the index being useful - the index becomes as big as the text! On top of that, each time a page or database row is updated or removed, the reference or index also has to be updated. So indexes speed up finding data, but slow down inserting, updating or deleting data. Some fields are automatically indexed. A primary key or a field marked as 'unique' - for example an email address, a userid or a social security number - are automatically indexed so the database can quickly check to make sure that you're not going to introduce bad data. So when should a database field be indexed? The general rule is anything that is used to limit the number of results you're trying to find. It's hard to generalise so we'll look at some specific but common examples. Note - the database tables shown below are used as an example only and will not necessarily be the best setup for your particular needs. In a database table that looks like this: Note: The SQL code shown below works with both MySQL and PostgreSQL databases. CREATE TABLE subscribers ( subscriberid INT PRIMARY KEY, emailaddress VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255) ); if we want to quickly find an email address, we create an index on the emailaddress field: CREATE INDEX subscriber_email ON subscribers(emailaddress); ... and any time we want to find an email address: SELECT firstname, lastname FROM subscribers WHERE emailaddress='email@domain.com'; ... it will be quite quick to find! |
|
