Django ListView with MySQL query for the queryset

Question

(Note: Even if you don't know Django, it's probably the query that needs work anyway, so you probably don't need to know Django/Python)

I have a ListView that displays all the entries in a mapping table between the pizza table and the topping table. Unfortunately, I can't just use Django's helpfulness and just give it the model to use (I'm skipping why for the sake of brevity), and have to use get_queryset instead. Unfortunately again, the load time is slow - there are almost 700 pizzas in the table and I need to use it in my sql query to order by pizza name. Here's what I'm currently doing:

class MapPizzaToppingListView(ListView):
    model = MapPizzaTopping
    paginate_by = 20
    template_name = 'onboardingWebApp/mappizzatopping_list.html'

    def get_queryset(self):
        cursor = connections["default"].cursor()
        cursor.execute("""SELECT map.*
                          FROM map_pizza_topping as map
                          INNER JOIN pizza
                              ON (map.pizza_id = pizza.id)
                          ORDER BY pizza.name""")
        queryset = dictfetchall(cursor)
        for row in queryset:
            pizza = Pizza.objects.get(id=row['pizza_id'])
            topping = Topping.objects.get(id=row['topping_id'])
            row['pizza_name'] = "%s (%s)" % (pizza.name, pizza.merchant.name)
            row['topping_name'] = topping.name
        return queryset

It takes a few seconds to load now, but we're going to be adding tons more pizzas eventually, so this really needs to be sped up. Unfortunately, my MySQL knowledge, though decent, isn't really to a point where I know how to make it better. What can I do to improve it?

---

Some more information that could be useful - the map_pizza_topping table just has a pizza_id column, a topping_id column, and a quantity column. The pizza table just has name, price, customer_id, and merchant_id columns, and the topping table just has name and is_dairy columns.

Answer 1

This is slow NOT because of your SQL. The SQL in the post is a simple JOIN, on id columns that are indexed by Django by default.

Without doubt, the cause of the slowness is this:

for row in queryset:
    pizza = Pizza.objects.get(id=row['pizza_id'])
    topping = Topping.objects.get(id=row['topping_id'])

For every row returned by the first query, you load a Pizza and a Topping. Django might be smart enough to not re-fetch the same topping multiple times, but for every unique Pizza and every unique Topping, Django will have to run an additional query. With 700 pizzas, you're looking at at least 700 queries, which is clearly not efficient.

Unfortunately, I can't just use Django's helpfulness and just give it the model to use (I'm skipping why for the sake of brevity)

It would be best to get to the bottom of this. Take a look at the documentation of the select_related method on querysets, I think the solution for your case should be somewhere around there. I really think there's a solution within the realm of "Django's helpfulness", without resorting to such queries, you just need to figure it out. (You might want to ask some questions on that on stackoverflow.com)

Another workaround might be to stop calling Pizza.objects.get and Topping.objects.get: include in your query all the fields you need for pizzas and toppings, and build simple dictionaries from them. Rest assured, when you stop calling Pizza.objects.get and Topping.objects.get 700 times you will notice a massive speed improvement.

Answer 2

This grabs everything you need in one query.

self.model.objects.select_related('pizza', 'pizza__merchant', 'topping').order_by('pizza__name')

What comes next depends on how the following two lines are being used.

row['pizza_name'] = "%s (%s)" % (pizza.name, pizza.merchant.name)
row['topping_name'] = topping.name

In the template you should be able to iterate over the object_list and get all of the attributes directly i.e. obj.pizza.name, obj.pizza.merchant.name, obj.topping.name.

Either way the above query gets everything and the data can be manipulated in memory as needed.