BoonEx forgets to create an INDEX in the URI of the bx_photos_main, which results in unusually slow browsing of the photos.
Because when user browse the previous and next photo, Is using the photo's URL to search for photos in MySQL.
In MySQL slow.log will find a select query that takes 2-5 seconds.
# Query_time: 1.335483 Lock_time: 0.000350 Rows_sent: 1 Rows_examined: 474884 use insoler; SET timestamp=1487295159; SELECT `bx_photos_main`.`ID` as `medID`, `bx_photos_main`.`Categories` as `Categories`, `bx_photos_main`.`Owner` as `medProfId`, `bx_photos_main`.`Title` as `medTitle`, `bx_photos_main`.`Uri` as `medUri`, `bx_photos_main`.`Desc` as `medDesc`, `bx_photos_main`.`Tags` as `medTags`, `bx_photos_main`.`Date` as `medDate`, `bx_photos_main`.`Views` as `medViews`, `bx_photos_main`.`Status` as `Approved`, `bx_photos_main`.`Featured` as `Featured`, `bx_photos_main`.`Rate` as `Rate`, `bx_photos_main`.`RateCount` as `RateCount`, `bx_photos_main`.`Ext` as `medExt`, `bx_photos_main`.`Size` as `medSize`, `bx_photos_main`.`Hash` as `Hash`, COUNT(`share1`.`ID`) as `Count`, `sys_albums`.`ID` as `albumId`, `sys_albums`.`Caption` as `albumCaption`, `sys_albums`.`Uri` as `albumUri`, `sys_albums`.`AllowAlbumView`, `sys_albums_objects`.`obj_order` FROM `bx_photos_main` LEFT JOIN `bx_photos_main` as `share1` USING (`Owner`) INNER JOIN `sys_albums_objects` ON `sys_albums_objects`.`id_object`=`bx_photos_main`.`ID` INNER JOIN `sys_albums` ON (`sys_albums`.`ID`=`sys_albums_objects`.`id_album` AND `sys_albums`.`Type`='bx_photos') WHERE `bx_photos_main`.`Uri`='14405226653176' GROUP BY `share1`.`Owner` LIMIT 1;
# Query_time: 4.736743 Lock_time: 0.000352 Rows_sent: 1 Rows_examined: 497338 SET timestamp=1500400274; SELECT `bx_photos_main`.`ID` as `medID`, `bx_photos_main`.`Categories` as `Categories`, `bx_photos_main`.`Owner` as `medProfId`, `bx_photos_main`.`Title` as `medTitle`, `bx_photos_main`.`Uri` as `medUri`, `bx_photos_main`.`Desc` as `medDesc`, `bx_photos_main`.`Tags` as `medTags`, `bx_photos_main`.`Date` as `medDate`, `bx_photos_main`.`Views` as `medViews`, `bx_photos_main`.`Status` as `Approved`, `bx_photos_main`.`Featured` as `Featured`, `bx_photos_main`.`Rate` as `Rate`, `bx_photos_main`.`RateCount` as `RateCount`, `bx_photos_main`.`Ext` as `medExt`, `bx_photos_main`.`Size` as `medSize`, `bx_photos_main`.`Hash` as `Hash`, COUNT(`share1`.`ID`) as `Count`, `sys_albums`.`ID` as `albumId`, `sys_albums`.`Caption` as `albumCaption`, `sys_albums`.`Uri` as `albumUri`, `sys_albums`.`AllowAlbumView`, `sys_albums_objects`.`obj_order` FROM `bx_photos_main` LEFT JOIN `bx_photos_main` as `share1` USING (`Owner`) INNER JOIN `sys_albums_objects` ON `sys_albums_objects`.`id_object`=`bx_photos_main`.`ID` INNER JOIN `sys_albums` ON (`sys_albums`.`ID`=`sys_albums_objects`.`id_album` AND `sys_albums`.`Type`='bx_photos') WHERE `bx_photos_main`.`Uri`='14016060603254' GROUP BY `share1`.`Owner` LIMIT 1;
I spent half a year to trace, and finally find the problem is out of this program : BxDolFilesDb.php
This MySQL script is too complicated and MySQL's bx_photos_main's Uri is not indexed
if (!$bSimple) { // album joins $oAlbum = new BxDolAlbums($this->_oConfig->getMainPrefix()); $sqlAlbumJoin = " INNER JOIN `{$oAlbum->sAlbumObjectsTable}` ON `{$oAlbum->sAlbumObjectsTable}`.`id_object`=`{$this->sFileTable}`.`{$this->aFileFields['medID']}` INNER JOIN `{$oAlbum->sAlbumTable}` ON (`{$oAlbum->sAlbumTable}`.`ID`=`{$oAlbum->sAlbumObjectsTable}`.`id_album` AND `{$oAlbum->sAlbumTable}`.`Type`='" . $this->_oConfig->getMainPrefix() . "') "; $sqlAlbumFields = "`{$oAlbum->sAlbumTable}`.`ID` as `albumId`, `{$oAlbum->sAlbumTable}`.`Caption` as `albumCaption`, `{$oAlbum->sAlbumTable}`.`Uri` as `albumUri`, `{$oAlbum->sAlbumTable}`.`AllowAlbumView`, `{$oAlbum->sAlbumObjectsTable}`.`obj_order`"; $sqlCount = "COUNT(`share1`.`{$this->aFileFields['medID']}`) as `Count`, "; $sqlCountJoin = "LEFT JOIN `{$this->sFileTable}` as `share1` USING (`{$this->aFileFields['medProfId']}`)"; $sqlGroup = "GROUP BY `share1`.`{$this->aFileFields['medProfId']}`"; } else $sqlFields = rtrim($sqlFields, ', '); $sqlQuery = "SELECT $sqlFields $sqlCount $sqlAlbumFields FROM `{$this->sFileTable}` $sqlCountJoin $sqlAlbumJoin WHERE $sqlCondition $sqlGroup LIMIT 1"; return $this->getRow($sqlQuery);