CODEWITHSUNDEEP
djangodjango-models
Tony
Tony

Reputation: 1057

What time would/wouldn't be good for setting db_index=True?

I know that I add indexes on columns when I want to speed up searches on that column.

Here is an example model

class Blog(models.Model):
    title = models.CharField(max_length=100)
    added = models.DateTimeField(auto_now_add=True)
    body = models.TextField()

I need to look up title and added columns and I should set db_index=True on that columns.

class Blog(models.Model):
    title = models.CharField(db_index=True, max_length=100)
    added = models.DateTimeField(db_index=True, auto_now_add=True)
    body = models.TextField()

But I search internet resource about more examples, I still can't understand or conclude how to use it. What time would/wouldn't be good for setting db_index=True?

Upvotes: 1

Views: 1019

Answers (2)

Rahul Gupta
Rahul Gupta

Reputation: 47846

When to consider adding an index to a column?

In general, you need to consider many points before deciding to add an index to a column.

Oracle in its docs, has defined multiple guidelines on when to add an index to a column: http://docs.oracle.com/cd/B19306_01/server.102/b14211/data_acc.htm#i2769

  • Consider indexing keys that are used frequently in WHERE clauses.
  • Consider indexing keys that are used frequently to join tables in SQL statements.
  • Choose index keys that have high selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value. Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.
  • Do not use standard B-tree indexes on keys or expressions with few distinct values. Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently, as in a high concurrency OLTP application.
  • Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.
  • Do not index keys that appear only in WHERE clauses with functions or operators. A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path that uses the index except with function-based indexes.
  • Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.
  • When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of the space required to store the index.

Remember when you add additional indexes, Read operations get faster but Write operations becomes slower because of recalculation of the indexes. So use them as per your use case demands.

Upvotes: 4

Aidan
Aidan

Reputation: 4250

The penalty for using indexes is slower write performance -- given you're unlikely to be posting a new blog post every 0.0001s you should feel free to add indexes for anything you're searching on.

Upvotes: 2

Related Questions