How does this change affect the speed of this database query?

Question

First i was using this request:

Model.object.get(name='somethink', date=somedatavariable)

But then I needed to extend my query and change it to this:

Model.objects.get(name__icontains="somethink", date__range(start_date,end_date))

Suddenly my database takes 5 times longer than with the original query. What's going on here, and how can I make it faster?

Answer 1

I doubt if anyone can guess, how exactly your database behaved :-) But you can use this middleware:

http://djangosnippets.org/snippets/161/

and check, what kind of sql queries were made and how much time they took.

Answer 2

A case-insensitive wildcard search (name__icontains='somethink') will DEFINITELY be a more expensive DB query than a case-sensitive exact match (name='somethink'). 5 times slower doesn't sound unreasonable, and it will vary heavily based on any and all of the following:

• Database Engine (MySQL, PostgreSQL, SQLite, etc.)
• Number of records in the DB
• Volume of text in each name field that it has to search through
• Whether or not there's an appropriate index the DB can use for that column

The last one there is very tricky. Doing full-text indexing for a database is very hard to get right, very easy to get wrong, and not even supported by all database engines.

The same goes for date vs. date__range: you're using a quick, simple, easy-to-index exact match in one, and an inexact match in the other.

The bottom line: if you don't need inexact matches, then don't use them. They're expensive DB operations and WILL take significantly longer.