سلام دوستان
امروز هاستینگی که سایت من روش قرار داره بهم یه اخطار داده و گفته سایتتون کوئریهای سنگین و طولانی مدت (بالای ده ثانیه) روی دیتابیس میزنه
من از جوملا ۲.۵ استفاده می کنم و کش جوملا رو هم فعال کردم، از اوپتیمایز و ریپیر mysql هم استفاده کردم اما تاثیر چندانی نداشت
اگه میشه راهنمایی کنید
کد:# Query_time: 9.876915 Lock_time: 0.000301 Rows_sent: 30 Rows_examined: 110760
--------------
# Time: 140315 13:14:02
# User@Host: s_fa[s_fa] @ localhost []
# Query_time: 9.876915 Lock_time: 0.000301 Rows_sent: 30 Rows_examined: 110760
use s_fa;
SET timestamp=1394876642;
SELECT DISTINCT u.id as userid, IFNULL(c.id,a.catid) as catid, a.sectionid as secid, a.id as id, a.*, a.introtext as itext, a.fulltext as mtext, u.name AS author, u.usertype, u.email as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, a.modified as updated, c.title as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN `jos_categories` AS c on c.id = a.catid
WHERE a.state='1'
AND IFNULL(c.id,0) IN (80,87,108,109,110,94,95,84,88,90,96,81,83,93,97,99,85,91,92,98,100,101,107,102,82,86,89,103,104,105)
AND (a.access = 1 OR a.access = 5)
AND (c.access = 1 OR c.access = 5)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-03-15 09:43:52')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-03-15 09:43:52')
ORDER BY a.created DESC LIMIT 30;
# Time: 140315 13:14:03
# User@Host: s_fa[s_fa] @ localhost []
# Query_time: 10.258272 Lock_time: 0.000246 Rows_sent: 30 Rows_examined: 110760
SET timestamp=1394876643;
SELECT DISTINCT u.id as userid, IFNULL(c.id,a.catid) as catid, a.sectionid as secid, a.id as id, a.*, a.introtext as itext, a.fulltext as mtext, u.name AS author, u.usertype, u.email as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, a.modified as updated, c.title as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN `jos_categories` AS c on c.id = a.catid
WHERE a.state='1'
AND IFNULL(c.id,0) IN (80,87,108,109,110,94,95,84,88,90,96,81,83,93,97,99,85,91,92,98,100,101,107,102,82,86,89,103,104,105)
AND (a.access = 1 OR a.access = 5)
AND (c.access = 1 OR c.access = 5)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-03-15 09:43:52')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-03-15 09:43:52')
ORDER BY a.created DESC LIMIT 30;
# Time: 140315 13:14:08
# User@Host: s_fa[s_fa] @ localhost []
# Query_time: 3.077208 Lock_time: 0.000147 Rows_sent: 30 Rows_examined: 110760
SET timestamp=1394876648;
SELECT DISTINCT u.id as userid, IFNULL(c.id,a.catid) as catid, a.sectionid as secid, a.id as id, a.*, a.introtext as itext, a.fulltext as mtext, u.name AS author, u.usertype, u.email as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, a.modified as updated, c.title as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN `jos_categories` AS c on c.id = a.catid
WHERE a.state='1'
AND IFNULL(c.id,0) IN (80,87,108,109,110,94,95,84,88,90,96,81,83,93,97,99,85,91,92,98,100,101,107,102,82,86,89,103,104,105)
AND (a.access = 1 OR a.access = 5)
AND (c.access = 1 OR c.access = 5)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-03-15 09:44:05')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-03-15 09:44:05')
ORDER BY a.created DESC LIMIT 30;
----------------
# Query_time: 3.730601 Lock_time: 0.000146 Rows_sent: 30 Rows_examined: 110760
----------------
# Time: 140315 13:14:11
# User@Host: s_fa[s_fa] @ localhost []
# Query_time: 3.730601 Lock_time: 0.000146 Rows_sent: 30 Rows_examined: 110760
SET timestamp=1394876651;
SELECT DISTINCT u.id as userid, IFNULL(c.id,a.catid) as catid, a.sectionid as secid, a.id as id, a.*, a.introtext as itext, a.fulltext as mtext, u.name AS author, u.usertype, u.email as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, a.modified as updated, c.title as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN `jos_categories` AS c on c.id = a.catid
WHERE a.state='1'
AND IFNULL(c.id,0) IN (80,87,108,109,110,94,95,84,88,90,96,81,83,93,97,99,85,91,92,98,100,101,107,102,82,86,89,103,104,105)
AND (a.access = 1 OR a.access = 5)
AND (c.access = 1 OR c.access = 5)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-03-15 09:44:07')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-03-15 09:44:07')
ORDER BY a.created DESC LIMIT 30;
# Time: 140315 13:14:16
# User@Host: s_fa[s_fa] @ localhost []
# Query_time: 5.592167 Lock_time: 0.000175 Rows_sent: 30 Rows_examined: 110760
SET timestamp=1394876656;
SELECT DISTINCT u.id as userid, IFNULL(c.id,a.catid) as catid, a.sectionid as secid, a.id as id, a.*, a.introtext as itext, a.fulltext as mtext, u.name AS author, u.usertype, u.email as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, a.modified as updated, c.title as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN `jos_categories` AS c on c.id = a.catid
WHERE a.state='1'
AND IFNULL(c.id,0) IN (80,87,108,109,110,94,95,84,88,90,96,81,83,93,97,99,85,91,92,98,100,101,107,102,82,86,89,103,104,105)
AND (a.access = 1 OR a.access = 5)
AND (c.access = 1 OR c.access = 5)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-03-15 09:44:11')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-03-15 09:44:11')
ORDER BY a.created DESC LIMIT 30;
-------------
# Query_time: 4.519286 Lock_time: 0.000145 Rows_sent: 30 Rows_examined: 110760
-------------
# Time: 140315 13:14:23
# User@Host: s_fa[s_fa] @ localhost []
# Query_time: 4.519286 Lock_time: 0.000145 Rows_sent: 30 Rows_examined: 110760
SET timestamp=1394876663;
SELECT DISTINCT u.id as userid, IFNULL(c.id,a.catid) as catid, a.sectionid as secid, a.id as id, a.*, a.introtext as itext, a.fulltext as mtext, u.name AS author, u.usertype, u.email as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, a.modified as updated, c.title as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN `jos_categories` AS c on c.id = a.catid
WHERE a.state='1'
AND IFNULL(c.id,0) IN (80,87,108,109,110,94,95,84,88,90,96,81,83,93,97,99,85,91,92,98,100,101,107,102,82,86,89,103,104,105)
AND (a.access = 1 OR a.access = 5)
AND (c.access = 1 OR c.access = 5)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-03-15 09:44:18')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-03-15 09:44:18')
ORDER BY a.created DESC LIMIT 30;
# Time: 140315 13:14:24
# User@Host: s_fa[s_fa] @ localhost []
# Query_time: 3.725389 Lock_time: 0.000180 Rows_sent: 30 Rows_examined: 110760
SET timestamp=1394876664;
SELECT DISTINCT u.id as userid, IFNULL(c.id,a.catid) as catid, a.sectionid as secid, a.id as id, a.*, a.introtext as itext, a.fulltext as mtext, u.name AS author, u.usertype, u.email as authorEmail, a.created_by_alias as authorAlias, a.created AS dsdate, a.modified as updated, c.title as catName, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug
FROM jos_content AS a
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN `jos_categories` AS c on c.id = a.catid
WHERE a.state='1'
AND IFNULL(c.id,0) IN (80,87,108,109,110,94,95,84,88,90,96,81,83,93,97,99,85,91,92,98,100,101,107,102,82,86,89,103,104,105)
AND (a.access = 1 OR a.access = 5)
AND (c.access = 1 OR c.access = 5)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-03-15 09:44:21')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-03-15 09:44:21')
ORDER BY a.created DESC LIMIT 30;
