search

jacksund / simmate

The Simulated Materials Ecosystem (Simmate) is a toolbox and framework for computational materials research.
https://simmate.org
BSD 3-Clause "New" or "Revised" License
30 stars 9 forks source link

Add database indexes #151

Open jacksund opened 2 years ago

jacksund commented 2 years ago

Common queries -- such as searching chemical_system__in on the CodStructure -- are extremely slow because there is no database index.

Django allows the creation of model indexes using the Meta.indexes option. I should explore adding these for common queries -- especially ones on the homepage for Simmate.

I've never made indexes before, so this may take some time to implement.

jacksund commented 2 years ago

Note, indexes from mix-ins won't be inherited automatically. We'll have to combine them manually. See here

jacksund commented 2 years ago

Alternatively, I can also set db_index=True to the fields which will be inherited properly. See here

jacksund commented 9 months ago

I now use db indexes all the time with small molecules (via db_index=True), and it's absolutely crucial in tables with >1million rows. Brings queries from >10s down to <0.1s in a bunch of cases.

So I'm surprised we haven't done this already for some core tables -- especially WorkItem and workflow tables involved in evolutionary search. tags, chemical_system, and other columns will definitely need them

scott-materials commented 9 months ago

@SWeav02 for future reference