Titre du sujet : Re: Surcharge serveur par escrime-info sur 20/09/2013 16:23:12
Je relance le sujet car mon hébergeur continue à me demander, à juste titre, d'optimiser mes requêtes SQL.
Les requêtes en cause sont toujours les mêmes ! Contenu du fichier slowlog !
# Time: 130918 7:56:42
# User@Host: xxxxx[xxxxx] @ localhost []
# Query_time: 16.258757 Lock_time: 0.000092 Rows_sent: 10 Rows_examined: 27235
use xxxxx;
SET timestamp=1379483802;
SELECT p.*, t.* FROM xxxxx_bb_posts AS p LEFT JOIN xxxxx_bb_posts_text AS t ON t.post_id = p.post_id WHERE (p.forum_id IN (17) AND p.approved = '1') ORDER BY p.post_time DESC LIMIT 8840, 10;
# Time: 130918 10:35:12
# User@Host: xxxxx[xxxxx] @ localhost []
# Query_time: 5.397629 Lock_time: 0.000083 Rows_sent: 1 Rows_examined: 63
use xxxxx;
SET timestamp=1379493312;
SELECT COUNT(*) FROM `xxxxx_bb_posts` WHERE (topic_id = '10203' AND approved = '1');
# Time: 130918 12:59:43
# User@Host: xxxxx[xxxxx] @ localhost []
# Query_time: 7.377828 Lock_time: 0.000098 Rows_sent: 1 Rows_examined: 146
use xxxxx;
SET timestamp=1379501983;
SELECT COUNT(*) FROM xxxxx_priv_msgs WHERE (read_msg = '0' AND to_userid = '5317');
# Time: 130918 14:27:21
# User@Host: xxxxx[xxxxx] @ localhost []
# Query_time: 9.648666 Lock_time: 0.000304 Rows_sent: 1 Rows_examined: 115
use xxxxx;
SET timestamp=1379507241;
SELECT COUNT(*) FROM xxxxx_priv_msgs WHERE (read_msg = '0' AND to_userid = '736');
# Time: 130918 14:44:50
# User@Host: xxxxx[xxxxx] @ localhost []
# Query_time: 14.943108 Lock_time: 0.000108 Rows_sent: 10 Rows_examined: 47836
use xxxxx;
SET timestamp=1379508290;
SELECT p.*, t.* FROM xxxxx_bb_posts AS p LEFT JOIN xxxxx_bb_posts_text AS t ON t.post_id = p.post_id WHERE (p.forum_id IN (21) AND p.approved = '1') ORDER BY p.post_time DESC LIMIT 15900, 10;
# Time: 130918 15:08:19
# User@Host: xxxxx[xxxxx] @ localhost []
# Query_time: 6.418801 Lock_time: 0.000153 Rows_sent: 1 Rows_examined: 40
SET timestamp=1379509699;
SELECT COUNT(*) FROM `xxxxx_bb_posts` WHERE (forum_id IN (16,50,53,54,55,56,2,9,7,10,12,14,17,19,24,30,34,35,36,46,1,4,6,44,45,5,21,40,25,28,29,47,13,23,22,49,26,42,8) AND uid = '1940' AND approved = '1');
# Time: 130918 15:37:54
# User@Host: xxxxx[xxxxx] @ localhost []
# Query_time: 33.210003 Lock_time: 0.000126 Rows_sent: 10 Rows_examined: 752104
use xxxxx;
SET timestamp=1379511474;
SELECT p.*, t.* FROM xxxxx_bb_posts AS p LEFT JOIN xxxxx_bb_posts_text AS t ON t.post_id = p.post_id WHERE (p.forum_id IN (16,50,53,54,55,56,2,9,7,10,12,14,17,19,24,30,34,35,36,46,1,4,6,44,45,5,21,40,25,28,29,47,13,23,22,49,26,42,8) AND p.approved = '1') ORDER BY p.post_time DESC LIMIT 214420, 10;
C'est donc newbb 3.08 qui est en cause. Comment éviter de surcharger le serveur mutualisé ? J'ai plus de 300.000 posts dans le forum...
Merci à vous !
|