MySQL 5.7 compatibility issue

[gjerdery]

After updating our mySQL server to version 5.7, we noticed that searches within the public Archon interface took a long time to complete. Most searches took around 6 minutes to complete, when the same search on an older version of mySQL was almost instantaneous.

Research led me to suspect the slowness was caused by how 5.7 now handles derived tables, and I was able to isolate a query in /packages/collections/lib/core/archon.inc.php as the one responsible for the slow-down. Slightly different versions of this query appear 4 times in this file (lines 1848, 2225, 2601, 3005).

A fix that worked for me was including the DISTINCT command with the internal SELECT for each query. E.G. at line 2225, I replaced:
$query = "SELECT tblCollections_Content.*, tblCollections_Collections.ClassificationID as ClassificationID FROM tblCollections_Content JOIN tblCollections_Collections ON tblCollections_Collections.ID = tblCollections_Content.CollectionID JOIN tblCollections_LevelContainers ON tblCollections_LevelContainers.ID = tblCollections_Content.LevelContainerID LEFT JOIN (SELECT ContentID FROM tblCollections_UserFields WHERE $userfieldquery) AS tblCollections_UserFields ON tblCollections_UserFields.ContentID = tblCollections_Content.ID WHERE ($textquery OR NOT (tblCollections_UserFields.ContentID IS NULL)) $subquery $enabledquery ORDER BY tblCollections_Content.SortOrder";

with:
$query = "SELECT tblCollections_Content.*, tblCollections_Collections.ClassificationID as ClassificationID FROM tblCollections_Content JOIN tblCollections_Collections ON tblCollections_Collections.ID = tblCollections_Content.CollectionID JOIN tblCollections_LevelContainers ON tblCollections_LevelContainers.ID = tblCollections_Content.LevelContainerID LEFT JOIN (SELECT DISTINCT ContentID FROM tblCollections_UserFields WHERE $userfieldquery) AS tblCollections_UserFields ON tblCollections_UserFields.ContentID = tblCollections_Content.ID WHERE ($textquery OR NOT (tblCollections_UserFields.ContentID IS NULL)) $subquery $enabledquery ORDER BY tblCollections_Content.SortOrder";

This has resolved my search speed issues, and I haven't discovered any knock-on consequences.

I have NOT been able to test backward compatibility with older versions of mySQL, and so haven't submitted it as a pull request yet.

[tpwalsh]

We're seeing the same issue with mariaDB5.5 from a box running PHP5.6.31 Thanks for the help! I know you saved me hours of tracking down the exact lines of code.

mysql --version
mysql Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1