Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django: Specifying HASH instead of BTREE for indexed column

Is there a good way within Django models to specify a specific index storage type?

For example, the default storage type for MySQL is BTREE, when for my particular column it may be more efficient to have HASH(hash table) as the storage type.

I can't find a good way without creating a custom field, or modifying django core, that will do this for me. I can also accomplish this by modifying the index after the table is created.

This situation probably doesn't matter for most things, but there are situations where a hash table is a more efficient lookup mechanism, and of course where sorting on the column is either not necessary or it doesn't make sense. For example a column with randomly generated data usually doesn't make a sensible ordering of information(unless you're looking for a repeatable random sort-- but that's beside the point).

like image 849
Kekoa Avatar asked Jul 19 '10 23:07

Kekoa


2 Answers

After a look at the code for django.db.backends.mysql.creation.sql_indexes_for_field(), it appears that there is no way to specify an index type in Django itself (there's no way to get a USING parameter into the query, unless you want to subclass the backend).

The best way around this limitation is to modify the index after the table has been created, as you suggested. You can use Django's backend-specific initial SQL data functionality for that. Simply create an "sql" directory in your app, and create an SQL file called <your_modelname>.mysql.sql inside it. Then put the SQL for modifying the index type in there, and Django will execute it after "syncdb" or "reset" calls affecting your model's table.

like image 167
Aram Dulyan Avatar answered Sep 21 '22 16:09

Aram Dulyan


The currently accepted answer needs an update. Also, this looks like a duplicate of this one: Python Django: how to select the index type?

Not sure what I can do about it so I'll just copy-paste my answer to the other thread:

Things have changed a lot since the question was asked. It is now possible to define an index in the model layer through the metaclass of your model (at least since django 1.11): https://docs.djangoproject.com/en/dev/ref/contrib/postgres/indexes/

For example:

from django.contrib.postgres.indexes import HashIndex
from django.db import models


class User(models.Model):
    name = models.CharField(max_length=100)

    class Meta:
        indexes = (HashIndex(fields=('name',)),)

like image 33
Babar Avatar answered Sep 20 '22 16:09

Babar