Indexing

Created on Dec. 22, 2012, 7:59 p.m. by Hevok & updated by Hevok on May 2, 2013, 5:24 p.m.

Relational database like MySQL or Postgres allow to index for a specific attribute to speed up operations like ordering or sorting by this attribute.

For instance, the data entry model looks in principle like following:

class Entry(models.Model):
    title = models.CharField(max_length=255)
    text = models.Textfield()
    created = models.DateTimeField(auto_now_add=True)

Now assuming there are 2000 entries in the database and they need to be listed in order they were created or by title. Without indexes it needs to scan over 2000 rows to figure out the order.

Here is an optimized version of the data entry model:

class Entry(models.Model):
    title = models.CharField(db_index=True, max_length=255)
    text = models.TextField()
    created = models.DateTimeField(db_index=True, auto_now_add=True)

This is better, because now there are indexes and the database just needs to take a look over the list of indexes which is a lot faster.

When are indexes appropriate? A good rule is if the field is going to be ordered by, or filtered by, it should be indexed.

indexing.png

Tags: optimization, models, rest, database, django
Categories: Tutorial, News, reST
Parent: Web Framework

Update entry (Admin) | See changes

Comment on This Data Unit