MySQL is particularly bad at optimizing subqueries, so in cases like
the query in Interface.networks it would do a full table scan to
find networks that match the given parent Network IDs.
This change forces the inner query to be evaluated by wrapping it with
list(), making this two queries that MySQL can efficiently optimize.
To test this, I used pynsot's CLI client in a for loop, creating 10
interfaces with a dataset that's similar to our prod dataset with about
20,000 assignments:
time (for i in {0..9}; do echo $i; nsot interfaces add -D 30167 -n "eth$i"; done)
Without this change, creating 10 interfaces took 9.606 seconds. After,
it took 4.723 seconds.
MySQL is particularly bad at optimizing subqueries, so in cases like the query in
Interface.networksit would do a full table scan to find networks that match the given parent Network IDs.This change forces the inner query to be evaluated by wrapping it with
list(), making this two queries that MySQL can efficiently optimize.It turns out that there's even a blurb in the Django docs about this, so we're not the first to run into this: https://docs.djangoproject.com/en/1.8/ref/models/querysets/#nested-queries-performance
To test this, I used pynsot's CLI client in a for loop, creating 10 interfaces with a dataset that's similar to our prod dataset with about 20,000 assignments:
Without this change, creating 10 interfaces took 9.606 seconds. After, it took 4.723 seconds.