Defective SQL Query

[Valtiel]

Hi there, let me start out by saying most of this is dutch to me so I might need answers dumbed down. I've been having massive problems with my gallery (dean-ambrose.net/gallery) with slowness and time outs and it's led to numerous upgrades with my dedicated server that haven't fixed anything. Eventually one of the hosts agents sent me this email:

The slowness throughout the day was caused by queries from the image gallery generating large temp tables. One of the queries I found that was causing problems can be found below:

==========
SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id
FROM cpg15x_pictures AS r
INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356) AND (1 OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%Jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%')
AND approved = 'YES'
ORDER BY ctime DESC, pid DESC
LIMIT 16178 ,1
==========

In order to prevent further high load a developer should look into optimizing the queries generated by the image gallery.

I didn't understand what that meant and asked them to explain a bit and received this:

The problems are coming from a defective SQL query being generated by your CopperMine installation that consumes a large amount of resources. Since the image gallery on dean-ambrose.net contains large number of images (over 300,000 according to the number of rows in the images table), the temporary table that CopperMine generates to sort through which images to display is very large. Since we do not offer development support, I had previously recommended that a developer or CopperMine's community be contacted to see if anything can be done to make CopperMine more efficient.

So there we have it, is there a way to fix this? Thanks!

[Αndré]

I can see 58 times

OR keywords like '%jen%'

which is of course nonsense and should be optimized.

Furthermore, do you intentionally use the album keyword feature? If not, this could probably be optimized very easy and quickly by removing the keywords from all albums.

[gmc]

While certainly nonsense from a coding standpoint, the entire portion of code within the parenthsis:

Code Select Expand


(1 OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%Jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%')

should have no bearing on performance... As the first comparison in the long OR clause is a '1' - which makes the OR true with no further analysis... Any SQL engine should recognize that and cease processing the remaining criteria. (I am assuming MySQL recognizes this...)

Still should be fixed, but I wouldn't't expect a performance improvement.

I think the inner joins are more likely the issue - with over 300,000 pics per quote:

(over 300,000 according to the number of rows in the images table)

Searching the code - I am not seeing anything like this query... Is this from a mod/plugin?? 
Or did you recognize it from something Αndre?

What plugins are installed in this gallery?

[Valtiel]

I did a re-install/update of coppermine just to see if it would help so maybe that got rid of the query? I wasn't even aware there was a keywords feature but if its 'Allow users to assign album keywords' then thats not checked.

No plugins installed.

[Αndré]

Searching the code - I am not seeing anything like this query... Is this from a mod/plugin?? 
Or did you recognize it from something Αndre?

I think it's the query in get_pic_data:

Code (include/functions.inc.php) Select Expand

        $query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} AS r
                    WHERE ((aid = $album $forbidden_set_string ) $keyword)$approved
                    ORDER BY $sort_order
                    $limit";

        $result = cpg_db_query($query);

which is used e.g. in display_thumbnails, display_film_strip and display_slideshow.

[gmc]

hmmmm... Except there are 2 INNER JOINs between "AS r" and "WHERE" that I'm not seeing...
Testing with my gallery at 1.5.26 and Debug on:

Code Select Expand


    [41] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id
                FROM cpg_pictures AS r
                INNER JOIN cpg_albums AS a ON a.aid = r.aid
                INNER JOIN cpg_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 5 AND 6) AND (1 OR keywords like '%oops%' OR keywords like '%test%')
                AND approved = 'YES'
                ORDER BY ctime DESC, pid DESC
                 LIMIT 0 ,6 [include/functions.inc.php:1504] (0.78 ms)

In functions.inc.php starting lines 1496 - 1504:

Code Select Expand


        $query = "SELECT $select_columns
                FROM {$CONFIG['TABLE_PICTURES']} AS r
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                ORDER BY ctime $DESC, pid $DESC
                $limit";

        $result = cpg_db_query($query);

It is indeed in get_pid_data - processing 'lastup' or last uploads...

Valtiel,
Can you turn on debug in config (for admin only if fine, then login as admin), and capture the queries used to generate a category view (index.php?cat=x) substitute number for x...  If some categories display faster than others - choose a slower one...

After page displays, at bottom will be 'Debug Info' with show/hide. Show, and look for list of queries... (below User and User Data)
Copy/paste into a post...
Looking for not just the queries - but also provides the processing time for each query to verify where we are spending our time... (0.78ms in my example above - in a very small sandbox gallery...)

I wasn't even aware there was a keywords feature but if its 'Allow users to assign album keywords' then thats not checked.

Admin can still set keywords... At least one album appears to have a keyword of 'jen' (and I'm guessing more than one does...)
Using a tool like phpMyAdmin run this SQL:

Code Select Expand

SELECT aid, title, keyword FROM cpg_albums WHERE keyword != '';
(replacing cpg_ with your table prefix)
and post results....

Thanks!
Greg

[Valtiel]

Hiya, I hope I've done this right this is all dutch to me. Heres the debug queries on our Screencaptures Category..

==========================
Queries:
------------------
Array
(
   

• => SELECT name, value FROM cpg15x_config [include/init.inc.php:179] (0.15 ms)
      [1] => SELECT * FROM cpg15x_plugins ORDER BY priority [include/plugin_api.inc.php:52] (0.04 ms)
      [2] => SELECT user_id, time FROM `jonmoxle_main`.cpg15x_sessions WHERE session_id = 'b1e49218ca5b741993684634dd33731e' [bridge/coppermine.inc.php:267] (0.22 ms)
      [3] => SELECT user_id, user_password FROM `jonmoxle_main`.cpg15x_users WHERE user_id = 2 [bridge/coppermine.inc.php:279] (0.95 ms)
      [4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `jonmoxle_main`.cpg15x_users AS u LEFT JOIN `jonmoxle_main`.cpg15x_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='2' [bridge/udb_base.inc.php:70] (0.2 ms)
      [5] => SELECT user_group_list FROM `jonmoxle_main`.cpg15x_users AS u WHERE user_id='2' AND user_group_list <> '' [bridge/coppermine.inc.php:205] (0.51 ms)
      [6] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS  priv_upl_need_approval FROM cpg15x_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (0.09 ms)
      [7] => SELECT group_name FROM cpg15x_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0.05 ms)
      [8] => SELECT COUNT(*) FROM cpg15x_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0.03 ms)
      [9] => SELECT lang_id FROM cpg15x_languages WHERE enabled='YES' [include/init.inc.php:330] (0.18 ms)
      [10] => SELECT user_favpics FROM cpg15x_favpics WHERE user_id = 2 [include/init.inc.php:388] (0.05 ms)
      [11] => DELETE FROM cpg15x_banned WHERE expiry < '2014-02-16 14:57:15' [include/init.inc.php:444] (0.23 ms)
      [12] => SELECT null FROM cpg15x_banned WHERE (user_id=2 OR '86.16.84.254' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:460] (0.05 ms)
      [13] => SELECT rgt, lft, depth FROM cpg15x_categories WHERE cid = 4 LIMIT 1 [include/functions.inc.php:48] (0.08 ms)
      [14] => SELECT cid FROM cpg15x_categories WHERE lft BETWEEN 157 AND 356 [include/functions.inc.php:54] (0.09 ms)
      [15] => SELECT keyword FROM cpg15x_albums WHERE category IN (4, 13, 14, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 71, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 104, 96, 97, 98, 100, 101, 102, 105, 106, 109, 112, 201, 116, 117, 137, 138, 139, 140, 143, 144, 145, 150, 151, 154, 155, 156, 161, 162, 167, 168, 169, 173, 174, 177, 178, 180, 193, 209, 202, 204, 205, 206, 207, 208, 215, 216, 217, 218, 222, 223, 224, 226, 227, 228) [include/functions.inc.php:62] (0.14 ms)
      [16] => SELECT p.cid, p.name FROM cpg15x_categories AS c,
                  cpg15x_categories AS p
                  WHERE c.lft BETWEEN p.lft AND p.rgt
                  AND c.cid = 4
                  ORDER BY p.lft [include/functions.inc.php:2839] (0.09 ms)
      [17] => SELECT name, description, thumb, depth FROM cpg15x_categories WHERE cid = 1 [index.php:270] (0.05 ms)
      [18] => SELECT COUNT(DISTINCT(p.aid)) AS alb_count, COUNT(*) AS pic_count
          FROM cpg15x_albums AS a
          INNER JOIN cpg15x_pictures AS p ON p.aid = a.aid
          WHERE a.category > 10000
          AND approved = 'YES' [index.php:294] (0.49 ms)
      [19] => SELECT name, description, cid, thumb, depth, lft
          FROM cpg15x_categories AS c
          WHERE depth BETWEEN 1 + 1 AND 1 + 1
  AND lft BETWEEN 157 AND 356
  ORDER BY c.lft [index.php:318] (669.28 ms)
      [20] => SELECT category, COUNT(*) AS num
          FROM cpg15x_albums AS a
          INNER JOIN cpg15x_categories ON cid = category
          WHERE depth BETWEEN 1 + 1 AND 1 + 1
  AND lft BETWEEN 157 AND 356
  GROUP BY category ORDER BY NULL [index.php:360] (0.68 ms)
      [21] => SELECT title, r.description, keyword, category, aid, alb_hits, visibility, r.thumb, r.owner
          FROM cpg15x_categories AS c
          INNER JOIN cpg15x_albums AS r ON r.category = c.cid
          WHERE c.depth BETWEEN 1 + 1 AND 1 + 1
  AND lft BETWEEN 157 AND 356
  ORDER BY r.pos, r.aid [index.php:381] (1972.61 ms)
      [22] => SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid, MAX(ctime) AS last_upload
          FROM cpg15x_categories AS c
          INNER JOIN cpg15x_albums AS r ON r.category = c.cid
          INNER JOIN cpg15x_pictures AS p ON p.aid = r.aid
          INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356) AND (1 OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%Jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%')
          AND approved = 'YES'
          AND c.depth BETWEEN 1 + 1 AND 1 + 1
          GROUP BY r.aid
          ORDER BY NULL [index.php:416] (48.12 ms)
      [23] => SELECT COUNT(*) FROM cpg15x_pictures WHERE approved = 'NO' [include/functions.inc.php:2421] (87.83 ms)
      [24] => SELECT lang_id, abbr FROM cpg15x_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2292] (0.1 ms)
      [25] => DELETE FROM cpg15x_temp_messages WHERE time < 1392559038 [include/functions.inc.php:4924] (12.41 ms)
      [26] => SELECT count(aid) FROM cpg15x_albums as a WHERE category = '4'  [index.php:713] (1.14 ms)
      [27] => SELECT COUNT(*)
                  FROM cpg15x_pictures AS r
                  INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                  INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356) AND (1 OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%Jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%')
                  AND approved = 'YES' [include/functions.inc.php:1487] (484.27 ms)
      [28] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip
                  FROM cpg15x_pictures AS r
                  INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                  INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356) AND (1 OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%Jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%')
                  AND approved = 'YES'
                  ORDER BY ctime DESC, pid DESC
                   LIMIT 0 ,10 [include/functions.inc.php:1504] (21044.61 ms)
      [29] => SELECT extension, mime, content, player FROM cpg15x_filetypes [include/functions.inc.php:5779] (0.13 ms)
      [30] => SELECT COUNT(*)
                  FROM cpg15x_pictures AS r
                  INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                  INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356) AND (1 OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%Jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%')
                  AND approved = 'YES'
                  GROUP BY r.aid [include/functions.inc.php:1844] (1094.72 ms)
      [31] => SELECT r.aid, a.thumb, a.keyword, a.alb_hits, a.title, MAX(ctime) AS ctime
                  FROM cpg15x_pictures AS r
                  INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                  INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356) AND (1 OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%Jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%')
                  AND approved = 'YES'
                  GROUP BY r.aid
                  ORDER BY ctime DESC
                   LIMIT 0 ,10 [include/functions.inc.php:1856] (967.75 ms)
      [32] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2034' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (79.94 ms)
      [33] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2027' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (230.21 ms)
      [34] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2018' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (51.13 ms)
      [35] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2013' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (48.66 ms)
      [36] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2010' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (54.58 ms)
      [37] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2009' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (61.81 ms)
      [38] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2003' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (48.36 ms)
      [39] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2002' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (111.62 ms)
      [40] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2001' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (55.45 ms)
      [41] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2000' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (58.45 ms)
      [42] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip
                      FROM cpg15x_pictures AS r
                      WHERE approved = 'YES'
                      AND r.pid IN (330503,329554,328771,327931,326882,326246,325785,325477,324964,323857) [include/functions.inc.php:1918] (2.66 ms)
  )

[Valtiel]

I tried running the phpmyadmin SQL query but I must be doing it wrong because it just says no database selected. I assumed I had to enter

SELECT aid, title, keyword FROM cpg15x_albums WHERE keyword != '';

I wish you could pay people to fix this stuff, so frustrated because I just don't understand it and this is such a major Gallery.

[Valtiel]

Incidentally I looked up deleting all keywords, went through the process and in keyword manager.. it doesn't even say I have any to manage. Yet all the folders have the keyword on it, and if I try to delete them one by one it doesn't do anything they just pop right back up. I don't know why this has suddenly started doing this! Its been fine for a year and suddenly, bam.

[gmc]

Valtiel,
The debug output is exactly right...
For the SQL, you do need to select at least the database containing CPG in the left pane of phpMyAdmin before issuing the SQL. (You can select the exact table -but since that is in the SQL statement it isn't required...)
I would still like to see that output if you can try again...
Depending on output - may have a change for you to try just to rule out a possible issue.

Based on what is in the debug - that page likely took over 30 seconds to load...
The top query took over 20 seconds (in runtime):

Code Select Expand


    [28] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip
                FROM cpg15x_pictures AS r
                INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356) AND (1 OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%Jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%' OR keywords like '%jen%')
                AND approved = 'YES'
                ORDER BY ctime DESC, pid DESC
                 LIMIT 0 ,10 [include/functions.inc.php:1504] (21044.61 ms)

This is the query I suspected from my testing - obtaining the last uploads...
*IF* this information isn't important to you - you can change the config setting for:
Album list view -> The content of the main page to remove  "/lastup,2" from the string (your number may differ from 2...)
That will eliminate the query from running - but also alter he display to not show the last uploaded files...

If this information is important to you - we need to keep looking...   
The next in line was under 2 seconds - so the one above is by far the biggest issue.

Incidentally I looked up deleting all keywords, went through the process and in keyword manager.. it doesn't even say I have any to manage. Yet all the folders have the keyword on it, and if I try to delete them one by one it doesn't do anything they just pop right back up. I don't know why this has suddenly started doing this! Its been fine for a year and suddenly, bam.

Keyword Manager seems to affect keywords assigned to pictures... I just deleted one in my test gallery, and it updated all the pics - but the album still contains the keyword...
If we can get the output of the SELECT above - I can give you an SQL statement to clean up what I think we will see there...

I wish you could pay people to fix this stuff, so frustrated because I just don't understand it and this is such a major Gallery.

Well - you can... there is a 'freelance' board to ask for paid help... but you do have a group of us here offering to assist for free - and depending where this leads - may need more than one of us to figure out.
We're happy to work with you.

[Valtiel]

Valtiel,
The debug output is exactly right...
For the SQL, you do need to select at least the database containing CPG in the left pane of phpMyAdmin before issuing the SQL. (You can select the exact table -but since that is in the SQL statement it isn't required...)
I would still like to see that output if you can try again...
Depending on output - may have a change for you to try just to rule out a possible issue.

Duh, okay I'll try that again in just a second.

This is the query I suspected from my testing - obtaining the last uploads...
*IF* this information isn't important to you - you can change the config setting for:
Album list view -> The content of the main page to remove  "/lastup,2" from the string (your number may differ from 2...)
That will eliminate the query from running - but also alter he display to not show the last uploaded files...

At this point I'll happily disable the feature. As useful as it is, I'd rather have it loading properly. It's okay to leave the last updated albums though?

If we can get the output of the SELECT above - I can give you an SQL statement to clean up what I think we will see there...

You're wonderful.

Well - you can... there is a 'freelance' board to ask for paid help... but you do have a group of us here offering to assist for free - and depending where this leads - may need more than one of us to figure out.
We're happy to work with you.

Thankyou. It's worth mentioning at this point that the server is undergoing an upgrade tomorrow to handle more traffic as that was a big problem for us. But this keyword thing has definitely been a huge bugbear so it's kinda been a two pronged mess attacking this Gallery.

[Valtiel]

Okay running the SQL Query brought up 7 pages of albums with the keywords on. Whoa. I don't even understand how they end up with a keyword because the weird part is it's not EVERY album and it's completely erratic and not just my username, fairly certain I never specified anything. I just uploaded pictures.

I won't post all 7 pages, just one of them.

Edit Edit
Copy Copy
Delete Delete
1298
Smackdown TV Taping, May 31st, 2013 (By Adam Jones...
jen

Edit Edit
Copy Copy
Delete Delete
1317
Exclusive: June 14th Smackdown continues during Co...
jen

Edit Edit
Copy Copy
Delete Delete
1363
Exclusive: June 28th Smackdown continues during Co...
jen

Edit Edit
Copy Copy
Delete Delete
1370
Exclusive: July 1st RAW Match continues during Com...
jen

Edit Edit
Copy Copy
Delete Delete
1372
WWE Facebook
jen

Edit Edit
Copy Copy
Delete Delete
1398
NXT TV Taping: July 11th, 2013 (By @nerdyjordy)
Jen

Edit Edit
Copy Copy
Delete Delete
1403
NXT TV Taping: July 11th, 2013 (By @DamnThatPanda)
Jen

Edit Edit
Copy Copy
Delete Delete
1405
Drake Younger
jen

Edit Edit
Copy Copy
Delete Delete
1434
Smackdown TV Taping: July 16th, 2013 (By Karina Sa...
Jen

Edit Edit
Copy Copy
Delete Delete
1446
Summer 2013 (Thanks to Laurie &amp; SinisterScotsm...
jen

Edit Edit
Copy Copy
Delete Delete
1468
Live Show: July 29th, 2013 in Adelaide, Australia ...
Linda

Edit Edit
Copy Copy
Delete Delete
1471
Exclusive: July 29th RAW Match continues during Co...
jen

Edit Edit
Copy Copy
Delete Delete
1491
August 8th, 2013
jen

Edit Edit
Copy Copy
Delete Delete
1494
August 7th, 2013
jen

Edit Edit
Copy Copy
Delete Delete
1496
RAW August 5th, 2013 (By Sarah @JGabePrincess)
jen

Edit Edit
Copy Copy
Delete Delete
1508
Live Show: July 28th, 2013 in Brisbane, Australia ...
jen

Edit Edit
Copy Copy
Delete Delete
1509
RAW June 17th, 2013 (By Dante Ken)
jen

Edit Edit
Copy Copy
Delete Delete
1516
August 12th, 2013
jen

Edit Edit
Copy Copy
Delete Delete
1528
NXT TV Taping: July 11th, 2013 (By @r3nays)
jen

Edit Edit
Copy Copy
Delete Delete
1552
Exclusive: August 19th RAW continues during Commer...
jen

Edit Edit
Copy Copy
Delete Delete
1565
August 21st, 2013
jen

Edit Edit
Copy Copy
Delete Delete
1573
Live Show: August 23rd, 2013 (By Victor)
jen

Edit Edit
Copy Copy
Delete Delete
1578
Tokyo, Japan 2013
jen

Edit Edit
Copy Copy
Delete Delete
1579
Adelaide, Australia 2013
jen

Edit Edit
Copy Copy
Delete Delete
1580
Anaheim August 2013
jen

Edit Edit
Copy Copy
Delete Delete
1590
August 26th, 2013
jen

Edit Edit
Copy Copy
Delete Delete
1599
August 30th, 2013
jen

Edit Edit
Copy Copy
Delete Delete
1604
Exclusive: August 30th Smackdown continues during ...
jen

Edit Edit
Copy Copy
Delete Delete
1615
October 2013
jen

Edit Edit
Copy Copy
Delete Delete
1623
Sabrina (@CenationRKO23)
jen

If thats even what you wanted to see. Doesn't seem to be any other info popped up.

[gmc]

Yes... It confirmed there are many (likely 58) albums with 'Jen' or 'jen' as a keyword... leading to the multiple 'OR keyword' clauses in the SQL...

Issue this SQL to eliminate:

Code Select Expand


UPDATE cpg15x_albums set keyword = '' WHERE keyword = 'jen';


Then try re-loading the page you posted earlier (if lastupd is still included) - and capture the debug again...
All the references to '%jen% should be gone... and we'll see what the elapsed time did...

Greg

[Valtiel]

You're a genius and I love you.

Queries:
------------------
Array
(
   

• => SELECT name, value FROM cpg15x_config [include/init.inc.php:179] (0.14 ms)
      [1] => SELECT * FROM cpg15x_plugins ORDER BY priority [include/plugin_api.inc.php:52] (0.05 ms)
      [2] => SELECT user_id, time FROM `jonmoxle_main`.cpg15x_sessions WHERE session_id = 'b1e49218ca5b741993684634dd33731e' [bridge/coppermine.inc.php:267] (0.08 ms)
      [3] => SELECT user_id, user_password FROM `jonmoxle_main`.cpg15x_users WHERE user_id = 2 [bridge/coppermine.inc.php:279] (0.05 ms)
      [4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `jonmoxle_main`.cpg15x_users AS u LEFT JOIN `jonmoxle_main`.cpg15x_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='2' [bridge/udb_base.inc.php:70] (0.05 ms)
      [5] => SELECT user_group_list FROM `jonmoxle_main`.cpg15x_users AS u WHERE user_id='2' AND user_group_list <> '' [bridge/coppermine.inc.php:205] (0.03 ms)
      [6] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS  priv_upl_need_approval FROM cpg15x_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (0.06 ms)
      [7] => SELECT group_name FROM cpg15x_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0.04 ms)
      [8] => SELECT COUNT(*) FROM cpg15x_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0.06 ms)
      [9] => SELECT lang_id FROM cpg15x_languages WHERE enabled='YES' [include/init.inc.php:330] (0.09 ms)
      [10] => SELECT user_favpics FROM cpg15x_favpics WHERE user_id = 2 [include/init.inc.php:388] (0.08 ms)
      [11] => DELETE FROM cpg15x_banned WHERE expiry < '2014-02-16 16:52:43' [include/init.inc.php:444] (0.15 ms)
      [12] => SELECT null FROM cpg15x_banned WHERE (user_id=2 OR '86.16.84.254' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:460] (0.04 ms)
      [13] => SELECT rgt, lft, depth FROM cpg15x_categories WHERE cid = 4 LIMIT 1 [include/functions.inc.php:48] (0.04 ms)
      [14] => SELECT cid FROM cpg15x_categories WHERE lft BETWEEN 157 AND 356 [include/functions.inc.php:54] (0.04 ms)
      [15] => SELECT keyword FROM cpg15x_albums WHERE category IN (4, 13, 14, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 71, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 104, 96, 97, 98, 100, 101, 102, 105, 106, 109, 112, 201, 116, 117, 137, 138, 139, 140, 143, 144, 145, 150, 151, 154, 155, 156, 161, 162, 167, 168, 169, 173, 174, 177, 178, 180, 193, 209, 202, 204, 205, 206, 207, 208, 215, 216, 217, 218, 222, 223, 224, 226, 227, 228) [include/functions.inc.php:62] (1.06 ms)
      [16] => SELECT p.cid, p.name FROM cpg15x_categories AS c,
                  cpg15x_categories AS p
                  WHERE c.lft BETWEEN p.lft AND p.rgt
                  AND c.cid = 4
                  ORDER BY p.lft [include/functions.inc.php:2839] (0.09 ms)
      [17] => SELECT name, description, thumb, depth FROM cpg15x_categories WHERE cid = 1 [index.php:270] (0.07 ms)
      [18] => SELECT COUNT(DISTINCT(p.aid)) AS alb_count, COUNT(*) AS pic_count
          FROM cpg15x_albums AS a
          INNER JOIN cpg15x_pictures AS p ON p.aid = a.aid
          WHERE a.category > 10000
          AND approved = 'YES' [index.php:294] (0.05 ms)
      [19] => SELECT name, description, cid, thumb, depth, lft
          FROM cpg15x_categories AS c
          WHERE depth BETWEEN 1 + 1 AND 1 + 1
  AND lft BETWEEN 157 AND 356
  ORDER BY c.lft [index.php:318] (0.08 ms)
      [20] => SELECT category, COUNT(*) AS num
          FROM cpg15x_albums AS a
          INNER JOIN cpg15x_categories ON cid = category
          WHERE depth BETWEEN 1 + 1 AND 1 + 1
  AND lft BETWEEN 157 AND 356
  GROUP BY category ORDER BY NULL [index.php:360] (0.53 ms)
      [21] => SELECT title, r.description, keyword, category, aid, alb_hits, visibility, r.thumb, r.owner
          FROM cpg15x_categories AS c
          INNER JOIN cpg15x_albums AS r ON r.category = c.cid
          WHERE c.depth BETWEEN 1 + 1 AND 1 + 1
  AND lft BETWEEN 157 AND 356
  ORDER BY r.pos, r.aid [index.php:381] (2 ms)
      [22] => SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid, MAX(ctime) AS last_upload
          FROM cpg15x_categories AS c
          INNER JOIN cpg15x_albums AS r ON r.category = c.cid
          INNER JOIN cpg15x_pictures AS p ON p.aid = r.aid
          INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356)
          AND approved = 'YES'
          AND c.depth BETWEEN 1 + 1 AND 1 + 1
          GROUP BY r.aid
          ORDER BY NULL [index.php:416] (30.32 ms)
      [23] => SELECT COUNT(*) FROM cpg15x_pictures WHERE approved = 'NO' [include/functions.inc.php:2421] (0.12 ms)
      [24] => SELECT lang_id, abbr FROM cpg15x_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2292] (0.09 ms)
      [25] => DELETE FROM cpg15x_temp_messages WHERE time < 1392565964 [include/functions.inc.php:4924] (0.19 ms)
      [26] => SELECT count(aid) FROM cpg15x_albums as a WHERE category = '4'  [index.php:713] (0.24 ms)
      [27] => SELECT COUNT(*)
                  FROM cpg15x_pictures AS r
                  INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                  INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356)
                  AND approved = 'YES' [include/functions.inc.php:1487] (62.48 ms)
      [28] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip
                  FROM cpg15x_pictures AS r
                  INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                  INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356)
                  AND approved = 'YES'
                  ORDER BY ctime DESC, pid DESC
                   LIMIT 0 ,10 [include/functions.inc.php:1504] (4820.99 ms)
      [29] => SELECT extension, mime, content, player FROM cpg15x_filetypes [include/functions.inc.php:5779] (0.15 ms)
      [30] => SELECT COUNT(*)
                  FROM cpg15x_pictures AS r
                  INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                  INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356)
                  AND approved = 'YES'
                  GROUP BY r.aid [include/functions.inc.php:1844] (122.19 ms)
      [31] => SELECT r.aid, a.thumb, a.keyword, a.alb_hits, a.title, MAX(ctime) AS ctime
                  FROM cpg15x_pictures AS r
                  INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                  INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356)
                  AND approved = 'YES'
                  GROUP BY r.aid
                  ORDER BY ctime DESC
                   LIMIT 0 ,10 [include/functions.inc.php:1856] (327.87 ms)
      [32] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2034' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (32.75 ms)
      [33] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2027' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (29.08 ms)
      [34] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2018' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (27.3 ms)
      [35] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2013' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (27.58 ms)
      [36] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2010' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (26.55 ms)
      [37] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2009' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (26.76 ms)
      [38] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2003' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (26.5 ms)
      [39] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2002' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (26.65 ms)
      [40] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2001' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (26.96 ms)
      [41] => SELECT pid FROM cpg15x_pictures WHERE ((aid = '2000' ) ) AND approved='YES' ORDER BY ctime DESC LIMIT 0,1 [include/functions.inc.php:1902] (26.46 ms)
      [42] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip
                      FROM cpg15x_pictures AS r
                      WHERE approved = 'YES'
                      AND r.pid IN (330503,329554,328771,327931,326882,326246,325785,325477,324964,323857) [include/functions.inc.php:1918] (0.31 ms)
  )
  

[gmc]

Big difference... The 'problem query' is now under 5 seconds... and many others no longer an issue at all.

Code Select Expand

    [28] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, pic_raw_ip, pic_hdr_ip
                FROM cpg15x_pictures AS r
                INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356)
                AND approved = 'YES'
                ORDER BY ctime DESC, pid DESC
                 LIMIT 0 ,10 [include/functions.inc.php:1504] (4820.99 ms)

Next in line is:

Code Select Expand

    [31] => SELECT r.aid, a.thumb, a.keyword, a.alb_hits, a.title, MAX(ctime) AS ctime
                FROM cpg15x_pictures AS r
                INNER JOIN cpg15x_albums AS a ON a.aid = r.aid
                INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 157 AND 356)
                AND approved = 'YES'
                GROUP BY r.aid
                ORDER BY ctime DESC
                 LIMIT 0 ,10 [include/functions.inc.php:1856] (327.87 ms)
Under half a second - and that is related to last album updated....

I expect that is loading much faster!

Glad I could help!
Please mark 'solved' if performance is now acceptable... (lower left corner of page)
Greg

[Valtiel]

I still get 'Critical Error processing database query' now and then. Maybe I'll just leave last uploads off, runs fine without it.

[gmc]

If you get that with Debug on - it will give more info on the failing query..
Start another thread with that info and we can look at it.
Greg

[Valtiel]

Okay thanks, will see how it is after our upgrade. Thanks again!

[Αndré]

I just had a closer look why Coppermine adds the same keyword several times to the query. As far as I can see they'll be added by $RESTRICTEDWHERE, which itself is generated inside the function get_meta_album_set:

Code (include/functions.inc.php) Select Expand

    if (!empty($CURRENT_ALBUM_KEYWORD)) {
        $RESTRICTEDWHERE .= ") AND (1";
        if(is_array($CURRENT_ALBUM_KEYWORD)) {
            foreach($CURRENT_ALBUM_KEYWORD as $keyword) {
                $RESTRICTEDWHERE .= " OR keywords like '%$keyword%'";
            }
        } else {
            $RESTRICTEDWHERE .= " OR keywords like '%$CURRENT_ALBUM_KEYWORD%'";
        }
    }

Unfortunately I don't get queries like you, so I cannot debug/optimize that any further.


For the record, your current gallery stats:

305,066 files in 1,972 albums and 214 categories

[gmc]

In this case, multiple (> 50) albums had the same keyword - so it got added 50+ times...
Based on the details in your post, Filtering duplicates from $CURRENT_ALBUM_KEYWORD array (either in its creation, or after) would correct this...

Of course the same keyword on 50+ albums wasn't really intended, but it shouldn't generate a query that sends MySQL into the weeds trying to process.