Slow search query - rewrite needed

Software Support
1

Hi,

OJS 3.4.0.9

I think there is a really big problem with this kind of search query that is killing a database. Do you think on rewriting this piece of code?

select s.* from submissions as s left join publications as po on s.current_publication_id = po.publication_id where s.context_id in (22) and s.status in (3) and (exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘colonial’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘colonial’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘colonial’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘educational’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘educational’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘educational’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘buildings’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘buildings’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘buildings’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘haydarpaşa’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘haydarpaşa’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘haydarpaşa’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘german’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘german’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘german’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘school’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘school’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘school’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘istanbul’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘istanbul’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘istanbul’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘turkey’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘turkey’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘turkey’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘late’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘late’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘late’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘ottoman’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘ottoman’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘ottoman’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘period’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘period’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘period’), ‘%’))) and s.submission_id not in (34941) order by (select COUNT(DISTINCT sskl.keyword_id) from submission_search_objects as sso inner join submission_search_object_keywords as ssok on ssok.object_id = sso.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where (sskl.keyword_text = LOWER(‘colonial’) or sskl.keyword_text = LOWER(‘educational’) or sskl.keyword_text = LOWER(‘buildings’) or sskl.keyword_text = LOWER(‘haydarpaşa’) or sskl.keyword_text = LOWER(‘german’) or sskl.keyword_text = LOWER(‘school’) or sskl.keyword_text = LOWER(‘istanbul’) or sskl.keyword_text = LOWER(‘turkey’) or sskl.keyword_text = LOWER(‘late’) or sskl.keyword_text = LOWER(‘ottoman’) or sskl.keyword_text = LOWER(‘period’)) and s.submission_id = sso.submission_id) desc, (select COUNT(0) from submission_search_objects as sso inner join submission_search_object_keywords as ssok on ssok.object_id = sso.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where (sskl.keyword_text = LOWER(‘colonial’) or sskl.keyword_text = LOWER(‘educational’) or sskl.keyword_text = LOWER(‘buildings’) or sskl.keyword_text = LOWER(‘haydarpaşa’) or sskl.keyword_text = LOWER(‘german’) or sskl.keyword_text = LOWER(‘school’) or sskl.keyword_text = LOWER(‘istanbul’) or sskl.keyword_text = LOWER(‘turkey’) or sskl.keyword_text = LOWER(‘late’) or sskl.keyword_text = LOWER(‘ottoman’) or sskl.keyword_text = LOWER(‘period’)) and s.submission_id = sso.submission_id) desc limit 10 offset 0

Regards.

2

Hi!

It’s a problem that will be fixed in OJS 3.6 Improve search framework · Issue #8920 · pkp/pkp-lib · GitHub

Until there you might use this plugin (I’ll make it available in the plugin gallery soon): GitHub - jonasraoni/fullTextSearch: Provides a database-backed full-text index for OJS/OMP/OPS, compatible with MySQL and PostgreSQL.

Best,
Jonas Raoni

3

@jonasraoni

Thank you for info.

Regards.

4

Hi @jonasraoni,

I installed the plugin, did reindex and enabled both options:

“Use full text search. When enabled the search interface will be powered by the plugin”
“Disable standard indexing. When enabled the application will stop feeding data to the standard search engine (the plugin will do it)“

However, i still see this mega queries running on my database. Is this expected behavior or something is not right? Thank you,

Regards.

5

This topic was automatically closed after 9 days. New replies are no longer allowed.