https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37389
Tomás Cohen Arazi <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |[email protected], | |[email protected] --- Comment #1 from Tomás Cohen Arazi <[email protected]> --- Pedro, before I got distracted, I found some interesting food for thought: * illrequestattributes is missing indexes on important fields like `type` and `value`. There's also missing indexes on other queried fields in other tables which could impact performance. * I understand the need to be able to process this kind of query: -and: [ { type: X, value: Y }, { type: W, value: Z } ] and hence the need for the code that was added to Query.pm. In this particular case, with only 79 requests on the DB, this is generating around 5M rows to later query on. Each different `type` is growing the size exponentially. Have we tried adding the conditions in the ON portion of the JOIN (not sure that's possible with DBIC, though). * This mechanism of making a JOIN per type would be useful for a form building boolean queries. But this is not the case. It seems to me that the query it is building doesn't have that structure I mentioned above. So we are paying the price, but not taking advantage of it at all. I feel like this is a bug in the sense the code should not be generating the JOINs in this case, as the query itself doesn't match the use case. And I didn't find a way to control this behavior either. * Looking at the generated query, I don't see how we cannot just use a single JOIN with a bit "more evolved" WHERE instead: SELECT * FROM illrequests LEFT JOIN illrequestattributes ON (illrequests.illrequest_id=illrequestattributes.illrequest_id) WHERE ( ( illrequestattributes.type='X' AND illrequestattributes.value LIKE '%fra%' ) ... OR ( illrequestattributes.type='Z' AND illrequestattributes.value LIKE '%fra%' ) ) That would only generate rows*(count_for different_types) rows: 632 vs. 5M. The problem here is the 5M rows with many left-hand wildcard searches on each (those wouldn't take advantage of the index). -- You are receiving this mail because: You are watching all bug changes. _______________________________________________ Koha-bugs mailing list [email protected] https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
