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);
