Speeding up sort

We’re seeing a load time of 14-15 seconds when sorting a result set of 10k items in Omeka Classic.

The same set of items loads in 2-3 seconds without the sort_field parameter. Smaller result sets, (around 3k) with a set sort_field parameter, load quickly as well.

Our current per page setting is 25 items/page.

I’ve disabled plugins to test their effect on slowness, but I didn’t find anything there. I also switched to a vanilla theme to test the effect of the custom theme. This didn’t improve speed either. I read @benbakelaar’s post from 2018 and I wonder if modifying queries has helped anyone with this issue. The site I’m troubleshooting lives on a shared server, so I’m not able to optimize or update the database ( MySQL 8.0.35)

What are my options for improving the speed?

Hello! Reviewing my old thread, it looks like the main improvements were 1) upgrade the MySQL/MariaDB server version, and 2) add that particular index to the tables (manually). Not sure if those are options for you, but I don’t think any other progress was ultimately made in other areas that were discussed.

Thanks @benbakelaar

It seems that sort should be faster. It’s been 5 years since Ben’s post. @jflatnes do you have any updates or wisdom to impart?

I don’t think anyone’s gone and written the kind of plugin I suggested there, which would probably be the biggest potential difference-maker in terms of sorting performance.

In terms of things you could more easily do now, a change to the table indexes as Ben mentions and as was mentioned in the previous thread may be the best first thing to try.

You can try running the following query against your database to add a new index, using the mysql command line client or something like phpMyAdmin. This assumes your prefix configured in db.ini is the default omeka_, adjust the table name as needed if it’s not:

ALTER TABLE `omeka_element_texts` ADD INDEX `sorting_improvement` (`record_type`, `record_id`, `element_id`, `text`(20));

There are other avenues like the configuration of the MySQL server and things like how much memory is used for buffers and things like that, but in a shared environment you probably won’t be able to adjust any of that.

Thanks so much! Adding the index improved speed dramatically for sort and advanced search.