Forums

Creating indexes after DB has been built for MYSQL DB

How can I get Django to create index after database is already built? Do I need to create them on the database tab?

I added 'db_index=True' to the columns that I want to become indexes in models.py.

Then I run this command:

    python manage.py makemigrations --name add_index_fake

Then I try to migrate the changes and I get this - it recognizes the change, but does not create an index:

  python manage.py sqlmigrate my_app migration_name
  BEGIN;
   --
   -- Alter field 'columnname' on table
   --
   COMMIT;

But I'm not getting this:

  CREATE INDEX xxxxxx ON "xxxx" ("sold_at");
  COMMIT;

That sounds very odd -- it looks like you've done everything correctly so I'd certainly expect the Django migration to contain an index. What are the contents of the .py file for the migration?

There is an index. Not sure why the search is so slow. The query takes 25 seconds to run on the server the first time. Every time after is again 3 seconds. Not sure what else to do. I tested it on my local and it takes 3 seconds, even the first time. It must have to do with my initialization of MYSQL on Pythonanywhere.

Here is the particular query. 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]

Giles, any thoughts on how to fix this? Is there some initialization process running? Is there an initialization mistake that I might have made?

I suspect the variation in speed is due to caching; the first query will have to pull the data from disk, but once it's in the MySQL server's RAM, you can query it more quickly. Of course, over time other queries might come in and cause the cached stuff to be evicted from memory, and then the next query would be slow.

There's a possibility that you're just resource-constrained on PythonAnywhere -- if your local machine is a regular laptop, then you have more dedicated resources there than you do on our site. (For price comparison, consider that a laptop costing $1,200 would last about a year when running at 100% CPU like servers normally do, so that essentially costs $100/month. Add on electricity, cooling, security, a fast internet connection, and so on, and it turns out that getting the performance of a normal-spec desktop as a hosted machine can cost $200/month easily.)

However, that particular query doesn't look enormously complicated and I'd certainly expect it to take less than 25 seconds. How do you get the contents of the bookstore_objects variable? What type is it, and how many objects does it contain?