Heavy query which causes high server load

[Dr_Michael]

I have noticed using this tool: www.dpgr.gr/status.php that there is a very strange query in my coppermine gallery, which takes some seconds (at least 1-2 seconds) to execute.

The query is:

Code Select Expand

SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, keywords,title, caption, owner_id, owner_name, aid, hits FROM usercpg11d_pictures WHERE approved = 'YES' AND aid IN (5,8,9,10,12,13,14,16,17,18,19,20,21,24,25,26,29,30,31,32,33,35,36,38,39,40,41,42,43,45,46,48,49,50,51,52,53,54,55,56,57,58,59,61,62,63,65,66,67,68,69,71,72, ... , 13377)  ORDER BY pid DESC  LIMIT 785 ,10


[Nibbler]

Looks like a perfectly normal query from several pages into the lastup meta album. The list of albums is so long because you have a lot of albums below that category.

[Dr_Michael]

Looks like a perfectly normal query from several pages into the lastup meta album. The list of albums is so long because you have a lot of albums below that category.

There is one main category "user galleries" where every user can create his albums. What should I do to optimise it?

[Nibbler]

Unless you want to dig through the code and optimize that special case,nothing.

[Dr_Michael]

Unless you want to dig through the code and optimize that special case,nothing.

That means its a disadvantage of coppermine since it cannot handle huge number of albums.

[Joachim Müller]

It can handle an unlimited number of albums (at least theoretically). You will have to make sure that the server is powerfull enough.

[Dr_Michael]

It can handle an unlimited number of albums (at least theoretically). You will have to make sure that the server is powerfull enough.

Fair enough. In my gallery with 130.000 photos of 19.000 members, hosted in the above server:
Dedicated server with 2 Dual Xeon 3,2 GHz (4 CPUs), 2 GB RAM and SATA HD, I do face problem.
What would you consider as a "powerful" server for such demands?

[Dr_Michael]

Unless you want to dig through the code and optimize that special case,nothing.

Anyone interested to optimise the code and get paid?

[Joachim Müller]

Moving to paid help section then.