Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: Slow SQL Queries  (Read 2106 times)

0 Members and 1 Guest are viewing this topic.

idosha

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 27
Slow SQL Queries
« on: October 23, 2023, 04:49:26 am »

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
Logged

406man

  • Contributor
  • Coppermine novice
  • ***
  • Offline Offline
  • Posts: 46
Re: Slow SQL Queries
« Reply #1 on: October 25, 2023, 06:27:34 pm »

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.
Logged
Pages: [1]   Go Up
 

Page created in 0.019 seconds with 20 queries.