Login

Composite Indexing for MySQL

Author:
fongandrew
Posted:
May 27, 2009
Language:
Python
Version:
1.0
Tags:
models mysql composite-indexing
Score:
0 (after 0 ratings)

A quick and dirty hack for composite indexing if you need it. Drop this into a models.py or some other place where it'll be loaded along with the rest of Django on start up.

Then add an _index_together tuple specifying the fields you want a composite index on.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
""" MySQL composite indexing. Also kinda works with SQLite3.

Looks for a _index_together tuple of column-name-tuples in the model class
Creates non-unique composite index based on that

Important : That tuple must be in the model, not the Meta class!
It's not a very Django-ish way of doing things, but I wasn't really willing
to hacking around in Django's Meta options. Meta ... it sounds so ... Ruby.

Usage is like so ...

class MyModel(models.Model):
    field_1 = models.IntegerField()
    field_2 = models.IntegerField()
    
    field_3 = models.IntegerField()
    field_4 = models.IntegerField()
    
    _index_together = (('field_1', 'field_2'), ('field3', 'field4'))

"""
from django.db import connection
from django.conf import settings


def create_index(model):
    meta = getattr(model, '_meta', None)
    if not meta: return 0
    
    if settings.DATABASE_ENGINE == 'mysql':
        func = create_index_mysql
    elif settings.DATABASE_ENGINE == 'sqlite3':
        func = create_index_sqlite3
    else:
        return 0
    
    successes = 0
    
    index_tuples = getattr(model, '_index_together', [])
    for index_tuple in index_tuples:
        columns = [meta.get_field(field).column for field in index_tuple]
        name = '_'.join(columns)[:63]
        table = meta.db_table
        successes += func(name, table, columns)
        
    if successes:
        print '%d indices created' % successes
    return successes


def create_index_mysql(name, table, columns):
    cursor = connection.cursor()    
    sql = "CREATE INDEX %s ON %s (%s)" % (
        name, table, ', '.join(columns))
        
    from MySQLdb import OperationalError
    try:
        cursor.execute(sql)
        return 1
    except OperationalError as x:
        if x.args[0] != 1061: # 1061 means duplicate key name / we can ignore
            raise
        return 0
    finally:
        cursor.close()


def create_index_sqlite3(name, table, columns):
    cursor = connection.cursor()
    sql = "CREATE INDEX IF NOT EXISTS %s ON %s (%s)" % (
        name, table, ', '.join(columns))
    try:
        cursor.execute(sql)
        return 1
    finally:
        cursor.close()


def create_all_indices(sender, *args, **kwds):
    from django.db import models
    model_list = models.get_models(sender)
    for model in model_list:
        create_index(model)


from django.db.models import signals
signals.post_syncdb.connect(create_all_indices)

More like this

Comments

Please login first before commenting.