User:Joe7rocks

Joe7 is a Gallery core-developer. :)

Email: jozsef.rnagy@gallery2.eu

Unefficiency problems with the ImageBlock module

The 'order by rand()' -which is used with the random pic- is most likely to end in very poor performance, at least when used with a table that has more than some 100s rows. (On my site i got this cost at somewhere around 1.5sec :o for a simple randompic query, which is to be executed 1x/pageview..)

something like this could help:

        if ($order == 'random') {
        $querypre='SELECT ROUND( RAND() * (SELECT MAX(g_itemId) FROM g2_ImageBlockCacheMap )) AS id';

        list ($ret, $resultspre) = ImageBlockHelper::_runQuery($querypre,array());
        if ($ret->isError()) {
            return array($ret->wrap(__FILE__, __LINE__), null);
        }

        $query='SELECT g_itemId
        FROM g2_ImageBlockCacheMap where g_userId = ? AND g_itemType = ? AND g_itemId >='.$resultspre[0]['id'].' ORDER BY g_itemId ASC';

It needs further tuning+join with the forbidden albums/picstable, but it is somewhere at 0.02 sec, with the the right indexing.

Further SLOW queries related to imageblock:

# Time: 051204  8:41:03
# User@Host: petg2[petg2] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 1  Rows_examined: 87672
SELECT COUNT(*) FROM g2_ImageBlockCacheMap WHERE g2_ImageBlockCacheMap.g_userId = 5;

# Time: 051204  8:41:46
# User@Host: petg2[petg2] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 145
SELECT COUNT(*) FROM g2_ImageBlockCacheMap WHERE g2_ImageBlockCacheMap.g_userId = 1242;
.
.

for all the users..

# Time: 051204  8:41:27
# User@Host: petg2[petg2] @ localhost []
'''# Query_time: 40'''  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
DELETE FROM g2_ImageBlockCacheMap;

^-- in every (x) minute? that's quite bad with 90K rows :o (must be triggered with something view related..) => Gallery::ViewableTreeChange refactor?.. more data into $event->getData() ? e.g. change is + or - ?..

'Cleanup' of PluginParameterMap?

Currently g2 stores usalbums mapping in g2_PluginParameterMap. It could move to a separate table.. (in my case it is 2k+ rows)

Themerelated setting could also fit nicely in other table.. theme matrix 156891 columns 3 (in my case it means 7k+ rows)