Debugging SQL with Django Debug Toolbar

By Michael Herman

In this tutorial, we'll be going over how to debug SQL queries using the django-debug-toolbar. Let's get started.

Please Note: This is a companion post to Learn Indexing, dammit!.


How often have you written queries using the Django ORM, just to forget about them a second later as soon as you get the results you're looking for? Even if you do drop down to writing SQL, it's still easy to forget that down the road certain queries can have a negative performance impact on your project.

Fortunately, the django-debug-toolbar can be used to identify processes that take up the most time when rendering a certain page.

Let's look at an example.


We need to install Django and create a Django Project and an App, django-debug-toolbar, and setup and populate a MySQL database with data.

  1. Start by cloning the repo from here:

    $ git clone

  2. Navigate into "django15-template" and setup/activate a virtualenv:

    $ virtualenv --no-site-packages env $ source env/bin/activate

  3. Install the requirements:

    $ pip install -r requirements.txt

  4. Setup a new database, if needed:

    $ mysql.server start $ mysql -u root -p Enter password: mysql> CREATE DATABASE ;

  5. Update the database settings in

    DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': '',
    'USER': 'root', 'PASSWORD': '', } }

  6. Navigate into the "django15" directory, and run the following commands to create your database tables and set up a superuser:

    $ python syncdb

  7. Create a new app:

    $ python startapp myapp

    Then add the app name to your file under INSTALLED_APPS.

You should now have a basic Django Project and App setup. For further asssitance follow along here.

Sanity Check

    1. Ensure that the development server is working and everything is setup thus far:

      $ python runserver

    2. You should see the following if everything is setup correctly:


    3. Your project structure should look like this:

      ├── django15
         │   ├──
         │   ├──
         │   ├──
         │   └──
         └── myapp


Models, Views, and URLs:

Add the following code to,,, and

  1. Models:

    from django.db import models import datetime

    class Comments(models.Model): title = models.CharField(maxlength=100) added = models.DateTimeField(autonow_add=True) body = models.TextField()

       def __unicode__(self): 
           return unicode(self.title)

    from django.http import HttpResponse from django.template import RequestContext, loader from .models import Comments

    def comments(request): t = loader.get_template('comments.html') c = RequestContext(request, {'comments': Comments.objects.all()}) return HttpResponse(t.render(c))

  3. URLs:

    from django.conf.urls import patterns, include, url

    from django.contrib import admin admin.autodiscover()

    urlpatterns = patterns('', url(r'^admin/', include(, url(r'^comments/', 'myapp.views.comments'), )

  4. Admin:

    from django.contrib import admin from .models import Comments

    class CommentsAdmin(admin.ModelAdmin): class Meta: model = Comments,CommentsAdmin)


  1. Update your template directory in

    For example:

    TEMPLATE_DIRS = ('/Users/michaelherman/desktop/django15-template/django15/templates')

  2. Add the following template, comments.html, to the newly created directory:

    Essentially, comments.html iterates through each contact from the Comments class and renders HTML for each.


    1. Sync your database again:

      $ python syncdb

    2. Add a new Comment in the Django Admin, fire up your server, then navigate to http://localhost:8000/comments/.

    3. There are the 3 SQL queries that take 2.18 ms to complete (this probably will vary slightly on your end). Take a look at the individual SQL queries to gain a deeper understanding of what's happening beneath the hood.
    4. Now, let's see what happens when we add A LOT more data and create a slightly more complex query.

    Add data and test

    1. Download output.sql from here, then run the following command to add data to MySQL:

      $ mysql -u root < output.sql

      This will take a few minutes to fully execute.

    2. Within the Django admin, check your database to make sure the new data is accessible. In all there should be now 30,000 objects in the myapp_comments table. Again, navigate to http://localhost:8000/comments/. We have 3 queries, but now it takes 1974.63 ms to complete.

    3. Add an ordering clause to the model:

      class Meta: ordering = ['-added']

      This only increases the time to 2073.47. Try ordering by another field or group the results. If you add another table, create a foreign key, and use a JOIN in your query.

    Next, let's see what happens when we add indexes. But first, let's drop the query down to raw SQL.

    • Remove the meta class from

      class Meta: ordering = ['-added']

    • Update the comments function() in

      def comments(request): t = loader.gettemplate('comments.html') c = RequestContext(request, {'comments': Comments.objects.raw('SELECT * FROM myappcomments ORDER BY added DESC;')}) return HttpResponse(t.render(c))

      This should speed it up slightly.


    1. Sync the database:

      $ python syncdb

    2. Create a database migration file with South:

      $ python schemamigration myapp --initial

    3. Migrate the database changes:

      $ python migrate myapp

    4. Add an index to the added field:

      added = models.DateTimeField(dbindex=True, autonow_add=True)

      Create a new migration file and then push the migration through.


    1. Test again. On my end, it's down to 694.48 ms:


    As you are going through your queries, ALWAYS drop down to SQL first, as this is generally faster than using the Django ORM, and pay close attention to any queries that use WHERE, ORDER BY, FILTER BY, and any JOINs.

See More Posts About:


Posted by Michael Herman

LinkedIn Website