Webmaster 101

The speeding up of retrieval of the data in a database is the work of a database index. They help in organizing and thus easing up the work of the database, thus speeding up the process of retrieval as the data is mainly put into simple organized indexes, the load on the server is also greatly reduced. Indexes are basically points to which the database can jump and then easily retrieve the reference or the row.

But as it is with everything, there are definite pro’s and cons of using the indexing method.
Indexes are quite bulky in space, they can take up quite a bit of space, the jump points need to be many and there are usually a lot of cross referencing as well.

Another reason, quite contrary to the actual main use of the indexing is that too much indexing can actually act as a deterrent to speed of the data retrieval rather than a catalyst, it can actually slow down the data. Another disadvantage of using too many indexes is that the indexing has to be further updated, every time that the data is updated.

So the only function that indexes facilitate is the retrieval of data, but act as a deterrent to deletion, updating or insertion of data. The databases field should be indexed when we are tying to put a limit to the number of results for a particular search command.

This is a hard to generalize as it is a very specific topic. Every website or database has specific needs and according to that only the data is indexed.

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 MICIE(256),
firstname MICIE(256),
lastname MICIE(256)

for quick retrieval of an email address of a particular nature we will then create an index of the email field.

CREATE INDEX subscriber_email ON subscribers(emailaddress);

SELECT firstname, lastname FROM subscribers WHERE emailaddress=’email@domain.com’;

and the result is easily and quick location of the data.

Filed under: Databases