OJS-3.4.0-5
MUSQL-8.0
Hi @jonasraoni
Some queries are running long and create a huge list of around 60 queries and after some time we face out of memory error.
Hi!
Iād say this is the worst part of the system regarding performance, Iām planning to work on it soon (Improve search framework Ā· Issue #8920 Ā· pkp/pkp-lib Ā· GitHub) 
You can try to apply the changes from this PR (it should improve a bit the situation, but not much):
This is what weāre using for now:
-
Decrease the quality of the search in exchange by performance (this code below should be placed on the file
classes/search/ArticleSearchDAO.php)
Handicapped search code for OJS 3.4 Ā· GitHub -
Limit the running time of queries (Iāve set to 20s) for the cases that are still problematic by adding a setting to the MySQL configuration:
max_execution_time=20000
Best,
Jonas Raoni
1 Like
OK, I will try to replace the code with provided file pkp/pkp-lib#9829 Attempt to improve the search query performance by jonasraoni Ā· Pull Request #4224 Ā· pkp/ojs Ā· GitHub and update you.
I have updated the missing CONSTRAINT and FOREIGN KEY. Letās see how it works for two-three days.
Okay, if it doesnāt help, thereās still the second approach, which will work
Best,
Jonas Raoni
Can we try replacing this fileās hole code (Handicapped search code for OJS 3.4 Ā· GitHub) with the current code file?
Yes. This will reduce the number of keywords that are searched from āunknownā to 1000 (you can try to increase the number at the line 89, if your server is behaving well, this is just the value that worked for me), so the user will see less results that normal, but itās better than not seeing anything.
Best,
Jonas Raoni
I also advice to setup the max_execution_time, I canāt think about a single query in the system that should take so much time to run, this will ensure that such long queries are killed, and then your database will not suffer so much.
Best,
Jonas Raoni
Hi,
I just did an upgrade to 3.4.0-7 and our site with around 100 journals is completely unreachable because of this query. Any new solutions/fixes?
Regards.
Hi!
Did you try applying the workaround Iāve shared previously?
Iām planning to write a search plugin as a proof of concept to do bigger changes in the codebase.
Best,
Jonas Raoni
Hi @jonasraoni,
Yes, but i didnāt have time to test it since our users reported big problems with reviews. Problem is described here: OJS review form settings get deleted/overwritten on upgrade from 3.2 to 3.3 so i had to revert back everything to our previous 3.2.1-4 version 
Thx. for help.
Regards,
Nino
Hi @jonasraoni Can you see why this query is putting heavy load on MySQL server. Due to this the performance is slow.
select `metrics_submission`.`submission_id`, SUM(metric) AS metric from `metrics_submission` where `context_id` in (5) and `assoc_type` in (515) and `date` between '2025-04-22' and '2025-05-21' group by `metrics_submission`.`submission_id` order by `metric` desc limit 5
Hi @jonasraoni I update the search code every time after upgrade. If I forget to apply it then the performance of MySQL server gets affected. I hope you are trying to upgrade the search query.
I increased the limit ($limit * 8) at line 89 and it is working.
Hi!
All the search code will be dropped in 3.6, regarding the performance:
- Now Iām working on a cache plugin, itās already working fine, but it just improves the performance for users which are not logged in: GitHub - jonasraoni/frontEndCache: Cache the frontend pages for OJS/OMP/OPS, Iām planning to add extra improvements this week, which perhaps will benefit the whole system if I can get it right
- Regarding the search, if you can use Solr, then you can try to use this plugin: GitHub - pkp/lucene: Plugin for Solr/Lucene support in OJS.. Given the problem will be solved in 3.6, Iām not sure Iāll write a search plugin, Iāll check internally. It should be pretty easy/simple, Iām just lacking the free time to do it.
Best,
Jonas Raoni