Why is so many sql queries?!

[masterzhan]

Hello, all!

I optimized my configuration of my gallery... Turned off everythinig (like counting comments, names, etc...)

I have:
(
   

• => SELECT extension, mime, content, player FROM filetypes; (0.007s)
      [1] => delete from `b16863`.sessions where time<1192119620 and remember=0; (0.004s)
      [2] => delete from `b16863`.sessions where time<1190913620; (0.004s)
      [3] => select user_id from `b16863`.sessions where session_id=md5("94fd9c14f0982eb5d8f418e387426f74ae8b47dbb6901f7e72db32de5d7bfd78"); (0.004s)
      [4] => select user_id as id, user_password as password from `b16863`.users where user_id=1 (0.035s)
      [5] => SELECT u.user_id AS id, u.user_name AS username, u.user_password AS password, u.user_group+100 AS group_id FROM `b16863`.users AS u INNER JOIN `b16863`.usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' (0.003s)
      [6] => SELECT user_group_list FROM `b16863`.users AS u WHERE user_id='1' and user_group_list <> ''; (0.005s)
      [7] => 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(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, 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, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM usergroups WHERE group_id in (1) (0.001s)
      [8] => SELECT group_name FROM  usergroups WHERE group_id= 1 (0.001s)
      [9] => update `b16863`.sessions set time='1192123220' where session_id=md5('94fd9c14f0982eb5d8f418e387426f74ae8b47dbb6901f7e72db32de5d7bfd78'); (0.001s)
      [10] => SELECT user_favpics FROM favpics WHERE user_id = 1 (0.03s)
      [11] => DELETE FROM banned WHERE expiry < '2007-10-11 17:20:22' (0.038s)
      [12] => SELECT * FROM banned WHERE (ip_addr='89.218.100.197' OR ip_addr='89.218.100.197' OR user_id=1) AND brute_force=0 (0.015s)
      [13] => SELECT cid, name, description, thumb FROM categories WHERE parent = ''  ORDER BY pos (0.034s)
      [14] => SELECT aid FROM albums as a WHERE category>=10000 (0.048s)
      [15] => SELECT count(*) FROM pictures as p, albums as a WHERE p.aid = a.aid AND approved='YES' AND category >= 10000 (0.046s)
      [16] => SELECT cid, name, description, thumb FROM categories WHERE parent = '1'  ORDER BY pos (0.049s)
      [17] => SELECT aid FROM albums as a WHERE category = '0' (0.13s)
      [18] => SELECT count(*) FROM albums as a WHERE 1 (0.018s)
      [19] => SELECT count(*) FROM pictures as p LEFT JOIN albums as a ON a.aid=p.aid WHERE 1 AND approved='YES' (0.04s)
      [20] => SELECT count(*) FROM comments as c LEFT JOIN pictures as p ON c.pid=p.pid LEFT JOIN albums as a ON a.aid=p.aid WHERE 1 (0.426s)
      [21] => SELECT count(*) FROM categories WHERE 1 (0.002s)
      [22] => SELECT sum(hits) FROM pictures as p LEFT JOIN albums as a ON p.aid=a.aid WHERE 1 (0.01s)
      [23] => SELECT COUNT(*) FROM pictures WHERE approved = 'NO' (0.003s)
      [24] => SELECT count(*) FROM albums as a WHERE category = '0' (0.104s)
      [25] => SELECT a.aid, a.title, a.description, category, visibility, filepath, filename, url_prefix, pwidth, pheight FROM albums as a LEFT JOIN pictures as p ON a.thumb=p.pid WHERE category=0 ORDER BY a.pos LIMIT 0,4 (0.005s)
      [26] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM albums AS a  LEFT JOIN pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (1, 2, 6, 8)GROUP BY a.aid (0.032s)
      [27] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM pictures WHERE pid='6560' (0.002s)
      [28] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM pictures WHERE pid='6557' (0.006s)
      [29] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM pictures WHERE pid='6559' (0.006s)
      [30] => SELECT filepath, filename, url_prefix, pwidth, pheight FROM pictures WHERE pid='6547' (0.001s)
      [31] => SELECT COUNT(*) from pictures WHERE approved = 'YES'  (0.003s)
      [32] => SELECT * FROM pictures WHERE approved = 'YES'  ORDER BY RAND() LIMIT 8 (0.169s)
  )
  
  Page generated in 3.292 seconds - 33 queries in 1.282 seconds - Album set : ; Meta set: ;
  
  Why is so many queries? How to optimize cpu loading?

[Nibbler]

You need to enable the MySQL query cache. Your server is either underpowered or overloaded judging by those numbers.

[masterzhan]

Your server is either underpowered or overloaded judging by those numbers.

What does it mean and why so think?

[Nibbler]

If it's not your server then you need to talk to your webhost. Definitely mention enabling the query cache.

[masterzhan]

If it's not your server then you need to talk to your webhost. Definitely mention enabling the query cache.

please, share with me - how to enable query cache? Where? Is it in my.ini? Or where i should enable this option?

[Nibbler]

Yes - review the MySQL documentation for details.

[masterzhan]

Yes - review the MySQL documentation for details.

Are there any other ways?

[Nibbler]

Look, either you have someone (ie, webhost or managed hosting) who runs your server for you and you ask them to do it or you run it yourself in which case it is your job to read the manual and learn.

[dke]

that isn't many queries, as noted, you need query cache... however if you want to lower the numbers of queries you can make all the album thumbnails static, instead of "last image uploaded" that should reduce it quiet some bit.. however i don't think 33 queries is any trouble anyway.

to enable query cache on windows mysql go to my.ini and add

Code Select Expand


query_cache_type = 1
query_cache_size = 128M


modify 128 to the number of MB you want to dedicate to your query cache (this depends a lot on how much ram you have in your server machine)

hope it helps.

[dke]

I forgot to add, that if you don't use a php optimizer you'll get a big slowdown as coppermine uses alot of php.

I personally use eAccellerator, google it up on how to install it for your php setup.

[Joachim Müller]

Running a webserver of your own (aka "Self-Hosting") is not recommended if you have no or little idea how to operate and maintain the server. As Nibbler suggested: RTFM and change the settings accordingly. If you're not willing to do so, then don't go for self-hosting, but shared webhosting. This board deals with Coppermine only. Asking for support how to set up and maintain a webserver is beyond the scope of this board.