Why are my column sorts slow in Django Admin, only 1 problem table

Question

I have a table (MySQL) with 10K+ records. Not a huge table. When working with the table in Django's Admin, the default sort order, the page loads blazing fast. Hardly noticeable.

But if I attempt to sort by any column, say by ID column or even a date column, it can take over a minute to return results. Other tables work fine

Any clues?

If more info is needed, add a comment to my question so I know what to add.

Thanks.

class Link(models.Model):
    from_page        = models.ForeignKey(Page, related_name='linked_to_set')
    to_page          = models.ForeignKey(Page, related_name='linked_from_set')
    data_source      = models.ForeignKey(Data_Source)
    moz_rank         = models.FloatField(blank=True, null=True, default=None)
    raw_score        = models.FloatField(blank=True, null=True, default=None)
    date             = models.DateField(auto_now_add=True)
    anchor_text      = models.TextField(blank=True, default='')
    hover_text       = models.TextField(blank=True, default='')
    no_follow        = models.BooleanField(blank=True, default=False)
    same_subdomain   = models.BooleanField(blank=True, default=False)
    meta_refresh     = models.BooleanField(blank=True, default=False)
    same_ip_address  = models.BooleanField(blank=True, default=False)
    same_c_block     = models.BooleanField(blank=True, default=False)
    redirect_301     = models.BooleanField(blank=True, default=False)
    redirect_302     = models.BooleanField(blank=True, default=False)
    no_script        = models.BooleanField(blank=True, default=False)
    off_screen       = models.BooleanField(blank=True, default=False)
    meta_no_follow   = models.BooleanField(blank=True, default=False)
    same_root_domain = models.BooleanField(blank=True, default=False)
    feed_autodiscovery = models.BooleanField(blank=True, default=False)
    rel_canonical      = models.BooleanField(blank=True, default=False)
    via_301            = models.BooleanField(blank=True, default=False)
    def __unicode__(self):
        return self.to_page.url

Answer 1

You need indexes on your data.

Add db_index=True to the fields that you will frequently want to sort by.

Ie. change:

models.DateField(auto_now_add=True)

to:

models.DateField(auto_now_add=True, db_index=True)

Don't add more than indexes than you really need, because they will slow inserts to the table and updates on the affected columns.

There is a lot more to indexing databases than this, but this might be a quick fix for your specific issue.