-
Hi, i’m moving my blog to a semi-dedicated and the host get a closer look to my site, btw we are trying to optimise his time loading and his cpu load
And he told me that i got 100 requests on the database by second,
So i desactivated all unecessary plugin and modules, but the request on the DB still here
I have a slowlog of the request needing more than 5sec to execute but i cannot understand it
The blog is a joomla 2.5.8, and the template is ja purity II is 2.5.3 release
I have also desactivated indexation of the articles and categories etc…
as told by the support of my host this morning :
The request that came back everytime is bellow she read 49,000 lign at each time
# Time: 130304 16:50:37
# User@Host: folamour[folamour] @ localhost [127.0.0.1]
# Query_time: 5.265360 Lock_time: 0.000387 Rows_sent: 5458 Rows_examined: 49548
use crashdebugfr;
SET timestamp=1362412237;
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name
as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id
is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ‘ ‘ THEN a.created_by_alias ELSE ua.name
END AS author,ua.email AS author_email,contact.id
as contactid,parent.title as parent_title, parent.id
as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id
is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_content_frontpage AS fp ON fp.content_id = a.idLEFT JOIN jos_categories AS c ON c.id
= a.catid
LEFT JOIN jos_users AS ua ON ua.id= a.created_by
LEFT JOIN jos_users AS uam ON uam.id= a.modified_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM jos_contact_details AS contact
WHERE contact.published = 1
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id= c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id= v.content_id
LEFT OUTER JOIN (SELECT cat.idas id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = ‘com_content’ AND parent.published != 1 GROUP BY cat.id
) AS badcats ON badcats.id
= c.id
WHERE a.access IN (1,1) AND c.access IN (1,1) AND CASE WHEN badcats.id
is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = ‘0000-00-00 00:00:00’ OR a.publish_up <= ‘2013-03-04 15:50:30’) AND (a.publish_down = ‘0000-00-00 00:00:00’ OR a.publish_down >= ‘2013-03-04 15:50:30’)
GROUP BY a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name
, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id
, c.title, c.path, c.access, c.alias, uam.id
, ua.name
, ua.email, contact.id
, parent.title, parent.id
, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id
, a.images, a.urls
ORDER BY fp.ordering, a.created;As you can saw its a bit touchy but i tried to optimize it by adding index without any improvment
If needed there is a function called « explain » well usefull under mysql (from phpmyadmin) to analyze who read what in a SELECT request
In this case she return :
+—-+————-+————+——–+———————————-+—————-+———+—————————-+——+————————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+———————————-+—————-+———+—————————-+——+————————————————-+
| 1 | PRIMARY | a | range | idx_access,idx_catid,publish_up | idx_access | 4 | NULL | 5535 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | c | eq_ref | PRIMARY,idx_access | PRIMARY | 4 | crashdebugfr.a.catid | 1 | Using where |
| 1 | PRIMARY | ua | eq_ref | PRIMARY | PRIMARY | 4 | crashdebugfr.a.created_by | 1 | |
| 1 | PRIMARY | uam | eq_ref | PRIMARY | PRIMARY | 4 | crashdebugfr.a.modified_by | 1 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | parent | eq_ref | PRIMARY | PRIMARY | 4 | crashdebugfr.c.parent_id | 1 | |
| 1 | PRIMARY | fp | eq_ref | PRIMARY | PRIMARY | 4 | crashdebugfr.a.id| 1 | |
| 1 | PRIMARY | v | eq_ref | PRIMARY | PRIMARY | 4 | crashdebugfr.a.id| 1 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 3 | DERIVED | parent | range | cat_idx,idx_left_right,extension | cat_idx | 153 | NULL | 2 | Using where; Using temporary; Using filesort |
| 3 | DERIVED | cat | range | idx_left_right | idx_left_right | 4 | NULL | 1 | Range checked for each record (index map: 0x20) |
| 2 | DERIVED | contact | system | idx_state | NULL | NULL | NULL | 1 | |
+—-+————-+————+——–+———————————-+—————-+———+—————————-+——+————————————————-+
12 rows in set (0.01 sec)And i’m posting here because this « dump » make me think to the way the article is displayed with ja purity II :
the blog is here http://www.crashdebug.fr
hope you can help,
[/URL]
This topic contains 4 replies, has 2 voices, and was last updated by 
We moved to new unified forum. Please post all new support queries in our New Forum