An Absolute Beginner’s Introduction to Database Indexes

Proper indexes on your tables are a crucial part of any Database design that requires advanced data retrieval. For basic databases with only a few dozen records per table, indexes may not be absolutely necessary and even slow things down (if your RDBMS does not automatically ignores the index), but it’s still good practice to design your database with proper indexes from the start if you expect it to grow big.

The minute your number of table records increases and you need to do more advanced select queries (eg. joining of multiple tables), efficiency and speed becomes important. That’s where indexes come into play.

In this post I’m going to give you an introduction tutorial/guide about database indexes, for everyone who has no idea what they’re used for. Basic SQL knowledge is required in order to comprehend everything. I’ll also show you a few examples on how to create them. Please note I’m using the MySQL RDBMS in my examples. Syntax may differ in other RDBMS, but the same principles apply.

Using an index for simple SELECT statements…

Let’s kick things off with a simple example. Let’s create a simple customer table first:

CREATE TABLE `kylescousin.com`.`customer` (
 `id` smallint(6) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(50) DEFAULT NULL,
 `last_name` varchar(50) DEFAULT NULL,
 `date_creation` datetime DEFAULT NULL,
 `date_update` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
);

Read more: Kyles Cousin

Posted via email from .NET Info