Slow Query 2.5.9 Large Content Table

[masoksian]

Hi, I have 138.000 and over content table on MYSQL 5.5.30 and INNODB storage and tables Quadcore server 8GB RAM INNODB buffer pool size=5G. and RAID 1 SATA III Disk.

My problem when adding new article saving very very slow (only more article in catageories, but a new blank categories in new article save no problem).

Look at the slow query log :

# Time: 130401 23:48:57
# User@Host: j25user[j25user] @ localhost []
# Query_time: 8.585393 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 126520
SET timestamp=1364849337;
UPDATE j25_assets
SET lft = lft + 2
WHERE lft > 78069;
# Time: 130401 23:49:08
# User@Host: j25user[j25user] @ localhost []
# Query_time: 10.799222 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 126520
SET timestamp=1364849348;
UPDATE j25_assets
SET rgt = rgt + 2
WHERE rgt >= 78069;
# Time: 130401 23:57:08
# User@Host: j25user[j25user] @ localhost []
# Query_time: 9.273784 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 126521
SET timestamp=1364849828;
UPDATE j25_assets
SET lft = lft + 2
WHERE lft > 78071;
# Time: 130401 23:57:21
# User@Host: j25user[j25user] @ localhost []
# Query_time: 13.113789 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 126521
SET timestamp=1364849841;
UPDATE j25_assets
SET rgt = rgt + 2
WHERE rgt >= 78071;
# Time: 130402 0:05:38
# User@Host: j25user[j25user] @ localhost []
# Query_time: 6.636521 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 126522
SET timestamp=1364850338;
UPDATE j25_assets
SET lft = lft + 2
WHERE lft > 78073;
# Time: 130402 0:05:48
# User@Host: j25user[j25user] @ localhost []
# Query_time: 9.087120 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 126522
SET timestamp=1364850348;
UPDATE j25_assets
SET rgt = rgt + 2
WHERE rgt >= 78073;



other slow query type:

# User@Host: [j25user] @ localhost []
# Query_time: 3.697140 Lock_time: 0.000234 Rows_sent: 9826 Rows_examined: 274418
SET timestamp=1364822797;
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 j25_content AS a
LEFT JOIN j25_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN j25_categories AS c ON c.id = a.catid
LEFT JOIN j25_users AS ua ON ua.id = a.created_by
LEFT JOIN j25_users AS uam ON uam.id = a.modified_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM j25_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 j25_categories as parent ON parent.id = c.parent_id
LEFT JOIN j25_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM j25_categories AS cat JOIN j25_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 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-04-01 13:26:28') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2013-04-01 13:26:28')
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 CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC , a.created;


I am controlled assetdiagnosticresults:

Asset Diagnostic

Is there any other asset with parent_id of 0? If no good if yes bad


Asset Table: no
Category Table: no
Is there any category without an asset in the asset table? if yes bad, if no good


com_banners: 0
com_contact: 0
com_content: 0
com_newsfeeds: 0
com_users.notes: 0
com_weblinks: 0
Is there any article without an asset in the asset table? if yes bad, if no good

yes
Is there any category with an asset level of < 2? If yes, bad, if no good


com_banners: no
com_contact: no
com_content: no
com_newsfeeds: no
com_users.notes: no
com_weblinks: no
Is there any article that has an asset level of < 3? If yes bad, if no good

no
Is there any article that has a parent_id that does not correspond to a category? if yes bad, if no good

no

I am trying ACL manager 2.3.1 and repaired. but still the same problem assetsdiagnostic reports.

Is there any article without an asset in the asset table? if yes bad, if no good

yes.

Please help me

[masoksian]

first category in 78.000 articles this category adding new article very slow. But second category in only 5 articles this category adding new article super fast.

As a cause of slowness is caused by the control for the lack of double-think the title of the content.

[HDsportsAT]

first category in 78.000 articles this category adding new article very slow. But second category in only 5 articles this category adding new article super fast.

As a cause of slowness is caused by the control for the lack of double-think the title of the content.

Do you have found a solution to solve the problem? i have the same problem...