Print

[idosha]

Can you please help me identify what exactly these 3 sql queries are from when loading the main gallery page. I assume they are "last updated, view count,etc" but I trying to figure out which is what and I'm not that familiar with SQL syntax.

I'm attempting to find out what queries we can remove from the albums list page to help our poor SQL server load data faster as each of these queries are taking over 8 seconds currently.

• SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid, MAX(ctime) AS last_upload, depth AS level, lft         
  FROM cpgqx_categories AS c         INNER JOIN cpgqx_albums AS r ON r.category = c.cid         INNER JOIN cpgqx_pictures AS p ON p.aid = r.aid         
  WHERE c.depth >= 0 + 1         
  AND approved = 'xxx'         AND r.aid NOT IN (992)                 
  GROUP BY r.aid         
  ORDER BY NULL
• SELECT r.aid, a.thumb, a.keyword, a.alb_hits, a.title, MAX(ctime) AS ctime                 
  FROM cpgqx_pictures AS r                 INNER JOIN cpgqx_albums AS a ON a.aid = r.aid                 
  WHERE (1)
  AND r.aid NOT IN (992)                 AND approved = 'xxx'                 
  GROUP BY r.aid                 
  ORDER BY ctime DESC                  LIMIT 0 ,10
• SELECT r.aid, a.thumb, a.keyword, a.alb_hits, a.title, MAX(ctime) AS ctime                 
  FROM cpgqx_pictures AS r                 INNER JOIN cpgqx_albums AS a ON a.aid = r.aid                 
  WHERE (1)
  AND r.aid NOT IN (992)                 AND approved = 'xxx'                 
  GROUP BY r.aid                 
  ORDER BY ctime DESC                  LIMIT 0 ,10

[406man]

I’ve had a quick look through the code of version 1.6.25 and found the following. As I’m an end user, not a member of the Coppermine team, I can’t be completely sure that it’s right without spending a lot more time on it. If the suggestions below don’t solve your problem, post on here a screenshot of the page that’s slow and the contents of the “The content of the main page” configuration item in the Album List View section.

The first query seems to be called from line 351 in index.php
This is in function get_subcat_data  so it’s displaying sub-category information.

In the Configuration settings, in the settings for the Gallery List View  is:
“Number of galleries to display”. Default is 2. Set it to 1  so that it only displays the top level categories.

The next two queries are the same and seem to be called from near line 1944 in  functions.inc.php
The function is get_pic_data which displays pictures.
Thearea of the  code deals with processing one of the configuration items - case “Lastalb” – which is the last album to which files have been uploaded

In the Album List View section of your gallery Configuration is an item called “The content of the main page”. It will be set to something like: breadcrumb/catlist/alblist/random,2/lastup,2

I believe yours contains    lastalb   somewhere.  Remove this and the slow queries won’t be run.