I have a Runway installation. The collection_index table has over 900,000 rows now. I'm only indexing the fields that I need for filtering/sorting.
So I have some performance issues. Particularly with perch_collection(). Pages that perform simple filtering with perch_collection() and get visited more frequently are fast to load though, so I'm assuming that's the result of SQL caching.
There are currently ~2300 items for all Collections. The site integrates with an external source to import new items and update existing ones which happens frequently. So the table keeps growing.
Although I'm trying to avoid this, but I may also have to import 20k+ archived items.
I'm trying to study what I can do to improve the performance here. Should I reach for something like Elasticsearch and use its queries instead of perch_collection()? Or should I perhaps build my own app so my database tables would have columns for each field I need for filtering/sorting (and fewer rows)? Is there something else I should be considering?