Change: Indexing

created on Dec. 22, 2012, 7:59 p.m. by Hevok & updated on Dec. 22, 2012, 7:59 p.m. by Hevok

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:

.. sourcecode:: python

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:

.. sourcecode:: python

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.


Categories: Tutorial, News, reST
Parent: Web Framework

Comment: Created entry.

See entry | Admin

Comment on This Data Unit