ISSUES: Forget to create an index in URI of photo

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);
bx_photos_main.jpg · 161.7K · 526 views
https://www.insoler.com/ The first community site supports RAW photo formats !
Quote · 2 Aug 2017

Thank you for the report:

https://github.com/boonex/dolphin.pro/issues/592

Rules → http://www.boonex.com/terms
Quote · 5 Aug 2017
 
 
Below is the legacy version of the Boonex site, maintained for Dolphin.Pro 7.x support.
The new Dolphin solution is powered by UNA Community Management System.