https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37389
--- Comment #2 from Pedro Amorim <[email protected]> --- I've been looking into this. I've gotten around the 5M rows by using a GROUP BY and running the query directly on CLI, it's still very slow. But I agree, 5M is obviously not expected or wanted. My exercise was much lighter and it was returning 46k rows when the expected result was 3, but the point stands, by using GROUP BY only the 3 results are returned, but it still takes like 15 seconds to return the query, and that's on a database with 10 ill requests. Some thoughts: 1) The datatable search input is only there because we moved to an ajax driven table, and that ILL table in particular has 8 extended_attribute columns, which will result in 8 left joins on the final query and rows are added to the final result exponentially. 2) The left-side filters for the ILL table work fine for the use case we want here I believe, and does the following query structure: OR ( ( `extended_attributes`.`type` = ? OR `extended_attributes`.`type` = ? OR `extended_attributes`.`type` = ? OR `extended_attributes`.`type` = ? OR `extended_attributes`.`type` = ? OR `extended_attributes`.`type` = ? OR `extended_attributes`.`type` = ? OR `extended_attributes`.`type` = ? ) AND `extended_attributes`.`value` LIKE ? ) Meaning it'll look for the occurrence of the keyword on any extended_attribute, even though it doesn't allow to filter for specific values on specific attributes. 3) My immediate suggestion for now is to disable the datatable search input and have users just use the left-side filters (and keyword search) instead, I can't think of a current use-case the left-side filters don't satisfy that the dt search does (neither allow to filter for specific values in specific attributes). 4) I've tried rewriting the way the query is built to maintain functionality and improve performance but so far I've been unsuccessful. I think this may take a while to find the proper final fix and this is why I'm suggesting the dt search input be disabled for now. -- 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/
