Bonjour les gens.
Mon hébergeur se plaint que j'exécute des requêtes SQL lentes... en
analysant le slow log de MySQL, il s'avère que toutes ces requêtes sont
liées à DotClear. Est-ce que vous avez une idée de ce qu'on peut faire pour
améliorer cela ?
Voici le fichier des requêtes lentes en fichier joint (ou là : http;//
mudry.org/fichiers/slow_queries.txt )
Note : mon installation de Dotclear2 est sur unstable.
Merci pour votre aide...
julien
Count: 1 Time=11.00s (11s) Lock=0.00s (0s) Rows=1.0 (1),
xxxblog[xxxblog]@host
SELECT count(comment_id) as somme from dc2_post billets, dc2_comment comments
where comments.post_id = billets.post_id AND billets.post_status=N AND
comment_status=N AND comment_trackback=N AND blog_id LIKE 'S'
Count: 1 Time=11.00s (11s) Lock=0.00s (0s) Rows=1.0 (1),
xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml,
post_notes, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type = 'S'
AND (C.cat_url = 'S' ) AND CONCAT(DATE_FORMAT(post_dt,'S') ,'S',P.post_id) >
'S' ORDER BY post_dt ASC LIMIT N
Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1),
xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type = 'S'
ORDER BY post_dt desc LIMIT N OFFSET N
Count: 1 Time=9.00s (9s) Lock=0.00s (0s) Rows=1.0 (1), xxxblog[xxxblog]@host
SELECT ses_value FROM dc2_session WHERE ses_id = 'S'
Count: 1 Time=8.00s (8s) Lock=0.00s (0s) Rows=16.0 (16),
xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml,
post_notes, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type = 'S'
AND DATE_FORMAT(post_dt,'S') = 'S' AND DATE_FORMAT(post_dt,'S') = 'S' ORDER
BY post_dt ASC
Count: 1 Time=8.00s (8s) Lock=0.00s (0s) Rows=28.0 (28),
xxxblog[xxxblog]@host
SELECT user_id, pref_id, pref_value, pref_type, pref_label, pref_ws FROM
dc2_pref WHERE user_id = 'S' OR user_id IS NULL ORDER BY pref_ws ASC, pref_id
ASC
Count: 8 Time=8.00s (64s) Lock=0.00s (0s) Rows=1.3 (10),
xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml,
post_notes, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type = 'S'
AND CONCAT(DATE_FORMAT(post_dt,'S') ,'S',P.post_id) > 'S' ORDER BY post_dt ASC
LIMIT N
Count: 1 Time=8.00s (8s) Lock=0.00s (0s) Rows=0.0 (0), xxxblog[xxxblog]@host
INSERT INTO `dc2_session` (
`ses_time`,
`ses_value`,
`ses_id`,
`ses_start`
) VALUES (
'S',
'S',
'S',
'S'
)
Count: 5 Time=7.80s (39s) Lock=0.00s (0s) Rows=1.4 (7), xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml,
post_notes, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type = 'S'
AND CONCAT(DATE_FORMAT(post_dt,'S') ,'S',P.post_id) < 'S' ORDER BY post_dt DESC
LIMIT N
Count: 4 Time=7.50s (30s) Lock=0.00s (0s) Rows=206.0 (824),
xxxblog[xxxblog]@host
SELECT blog_id, setting_id, setting_value, setting_type, setting_label,
setting_ns FROM dc2_setting WHERE blog_id = 'S' OR blog_id IS NULL ORDER BY
setting_ns ASC, setting_id DESC
Count: 2 Time=7.50s (15s) Lock=0.00s (0s) Rows=18.5 (37),
xxxblog[xxxblog]@host
SELECT C.cat_id, COUNT(P.post_id) AS nb_post FROM dc2_category AS C JOIN
dc2_post P ON (C.cat_id = P.cat_id AND P.blog_id = 'S' ) WHERE C.blog_id = 'S'
AND P.post_status = N GROUP BY C.cat_id
Count: 9 Time=7.44s (67s) Lock=0.11s (1s) Rows=1.0 (9), xxxblog[xxxblog]@host
SELECT DISTINCT(DATE_FORMAT(post_dt,'S') ) AS dt ,COUNT(P.post_id) AS nb_post
FROM dc2_post P LEFT JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type IN
('S') AND DATE_FORMAT(post_dt,'S') < 'S' GROUP BY dt ORDER BY dt desc LIMIT N
Count: 3 Time=7.33s (22s) Lock=0.00s (0s) Rows=16.0 (48),
xxxblog[xxxblog]@host
SELECT C.cat_id, COUNT(P.post_id) AS nb_post FROM dc2_category AS C JOIN
dc2_post P ON (C.cat_id = P.cat_id AND P.blog_id = 'S' ) WHERE C.blog_id = 'S'
AND P.post_status = N AND P.post_type IN ('S') GROUP BY C.cat_id
Count: 1 Time=7.00s (7s) Lock=0.00s (0s) Rows=1.0 (1), xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml,
post_notes, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type = 'S'
AND P.post_id IN (N) ORDER BY post_dt DESC
Count: 1 Time=7.00s (7s) Lock=0.00s (0s) Rows=1.0 (1), xxxblog[xxxblog]@host
SELECT M.media_file, M.media_id, M.media_path, M.media_title, M.media_meta,
M.media_dt, M.media_creadt, M.media_upddt, M.media_private, M.user_id,
PM.post_id FROM dc2_media M INNER JOIN dc2_post_media PM ON (M.media_id =
PM.media_id) WHERE PM.post_id IN ('S') AND M.media_path IN ('S') AND
PM.link_type='S'
Count: 1 Time=7.00s (7s) Lock=0.00s (0s) Rows=1.0 (1), xxxblog[xxxblog]@host
SELECT M.meta_id, M.meta_type, COUNT(M.post_id) as count FROM dc2_meta M LEFT
JOIN dc2_post P ON M.post_id = P.post_id WHERE P.blog_id = 'S' AND meta_type =
'S' AND meta_id = 'S' AND ((post_status = N AND post_password IS NULL ) )
GROUP BY meta_id,meta_type,P.blog_id ORDER BY count DESC
Count: 1 Time=7.00s (7s) Lock=0.00s (0s) Rows=21.0 (21),
xxxblog[xxxblog]@host
SELECT C2.cat_id, C2.cat_lft, C2.cat_rgt, COUNT(C1.cat_id) AS level ,
C2.cat_title, C2.cat_url, C2.cat_desc FROM dc2_category AS C1, dc2_category AS
C2 WHERE C2.cat_lft BETWEEN C1.cat_lft AND C1.cat_rgt AND C2.blog_id = 'S'
AND C1.blog_id = 'S' GROUP BY C2.cat_id, C2.cat_lft, C2.cat_rgt , C2.cat_title,
C2.cat_url, C2.cat_desc ORDER BY C2.cat_lft DESC
Count: 3 Time=7.00s (21s) Lock=0.00s (0s) Rows=13.7 (41),
xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml,
post_notes, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type = 'S'
ORDER BY post_dt desc LIMIT N OFFSET N
Count: 12 Time=6.58s (79s) Lock=0.00s (0s) Rows=1.0 (12),
xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml,
post_notes, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N ) ) AND post_type = 'S' AND post_url = 'S' ORDER BY
post_dt DESC
Count: 9 Time=6.56s (59s) Lock=0.00s (0s) Rows=1.0 (9), xxxblog[xxxblog]@host
SELECT B.blog_id, blog_uid, blog_url, blog_name, blog_desc, blog_creadt,
blog_upddt, blog_status FROM dc2_blog B WHERE NULL IS NULL AND blog_status IN
(N,N) AND B.blog_id = 'S' ORDER BY B.blog_id ASC
Count: 1 Time=6.00s (6s) Lock=0.00s (0s) Rows=1.0 (1), xxxblog[xxxblog]@host
SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz,
post_creadt, post_upddt, post_format, post_password, post_url, post_lang,
post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml,
post_notes, post_type, post_meta, post_status, post_selected, post_position,
post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name,
U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title,
C.cat_url, C.cat_desc FROM dc2_post P INNER JOIN dc2_user U ON U.user_id =
P.user_id LEFT OUTER JOIN dc2_category C ON P.cat_id = C.cat_id , dc2_meta META
WHERE P.blog_id = 'S' AND ((post_status = N AND post_password IS NULL ) ) AND
post_type = 'S' AND CONCAT(DATE_FORMAT(post_dt,'S') ,'S',P.post_id) > 'S' AND
META.post_id = P.post_id AND META.meta_id = 'S' AND META.meta_type = 'S'
ORDER BY post_dt ASC LIMIT N
Count: 1 Time=6.00s (6s) Lock=0.00s (0s) Rows=0.0 (0), xxxblog[xxxblog]@host
INSERT INTO `dc2_comment` (
`comment_author`,
`comment_site`,
`comment_email`,
`comment_content`,
`post_id`,
`comment_status`,
`comment_ip`,
`comment_spam_status`,
`comment_spam_filter`,
`comment_id`,
`comment_upddt`,
`comment_dt`,
`comment_tz`,
`comment_words`
) VALUES (
'S',
'S',
'S',
'S',
'S',
-N,
'S',
'S',
'S',
N,
'S',
'S',
'S',
'S'
)
Count: 1 Time=6.00s (6s) Lock=0.00s (0s) Rows=1.0 (1), xxxblog[xxxblog]@host
SELECT COUNT(post_id) as nb_post, post_lang FROM dc2_post WHERE blog_id = 'S'
AND post_lang <> 'S' AND post_lang IS NOT NULL AND post_type = 'S' GROUP BY
post_lang ORDER BY post_lang asc
Count: 1 Time=6.00s (6s) Lock=0.00s (0s) Rows=1.0 (1), xxxblog[xxxblog]@host
SELECT DISTINCT(DATE_FORMAT(post_dt,'S') ) AS dt ,COUNT(P.post_id) AS nb_post
FROM dc2_post P LEFT JOIN dc2_category C ON P.cat_id = C.cat_id WHERE P.blog_id
= 'S' AND ((post_status = N AND post_password IS NULL ) ) AND post_type = 'S'
AND DATE_FORMAT(post_dt,'S') = 'S' AND DATE_FORMAT(post_dt,'S') = 'S' GROUP
BY dt ORDER BY dt desc
Count: 1 Time=6.00s (6s) Lock=0.00s (0s) Rows=0.0 (0), xxxblog[xxxblog]@host
SELECT alias_url, alias_destination, alias_position FROM dc2_alias WHERE
blog_id = 'S' ORDER BY alias_position ASC
--
Dev mailing list - [email protected] - http://ml.dotclear.org/listinfo/dev
