0

I've been trying to optimize this Django query set search the last two days and have been unable to speed it up. My backend is MYSQL.

There are three tables: Book, Bookstore and Category. Book has 1 million observations, Bookstore has 500 observations and category has 10k observations

table = Book.objects.filter(Category=pk, bookstore__in=bookstore_objects).order_by('title').prefetch_related(Prefetch('bookstore', to_attr='bookstore_list'))[:50]

The first time this query is run-in the shell it takes 20+ seconds and subsequently it takes about 3-5 seconds. Please help me speed up this query. I cannot understand why it's so slow.

user10421193
  • 217
  • 2
  • 11
  • You're missing index. Paste the above code in django-shell and get the SQL query output by appending `.query` to it as described here: https://stackoverflow.com/questions/3748295/getting-the-sql-from-a-django-queryset Next, paste the above query in MYSQL shell prefixed with `EXPLAIN`. Mysql will tell whether it's using indexes or not. – Siddharth Srivastava Nov 10 '19 at 06:05
  • Would this be an index problem if it's only slow the first time and then subsequently pretty fast. The first time I run this query it takes 20+ seconds and after only 3-5 seconds. If it's an index issue, I thought that it would be the same speed every time,. But I could be wrong. – user10421193 Nov 10 '19 at 16:37
  • MySQL tends to cache a query's result after 1st execution. I am not exactly sure how the cache invalidates, but IIRC on any write to the table MySQL would invalidate the cache. – Siddharth Srivastava Nov 11 '19 at 03:18

0 Answers0