我从我的网络托管公司收到通知,以优化我的网站中的MySQL查询 . 他告诉我,共享主机不允许运行MySql查询超过15秒,而此域的mysql查询运行时间超过75秒 .
但我不知道该做什么以及在哪里改变以优化MySQL查询 . 请帮我解决这个问题 .
User@Host: khabarin_suprusr[khabarin_suprusr] @ localhost [] Query_time: 76.546533 Lock_time: 1.408959 Rows_sent: 5 Rows_examined: 3038
use khabarin_portal3;
SET timestamp=1347378163;
SELECT *
FROM (`art_conts`)
JOIN `art_cats` ON `art_cats`.`cat_id`=`art_conts`.`cat_id`
JOIN `art_secs` ON `art_secs`.`sec_id`=`art_cats`.`sec_id`
WHERE `art_conts`.`cont_status` = 'public'
ORDER BY `cont_id` desc
LIMIT 5;
**User@Host: khabarin_suprusr[khabarin_suprusr] @ localhost []strong text Query_time:54.002604 Lock_time:1.403354 Rows_sent:1 Rows_examined:3033 **
use khabarin_portal3;
SET timestamp=1347376289;
User@Host: khabarin_suprusr[khabarin_suprusr] @ localhost [] Query_time: 24.436448 Lock_time: 13.889496 Rows_sent: 1 Rows_examined: 0
use khabarin_portal3;
SET timestamp=1347376151;
SELECT COUNT(*) AS `numrows`
FROM (`art_conts`)
JOIN `art_cats` ON `art_cats`.`cat_id`=`art_conts`.`cat_id`
JOIN `art_secs` ON `art_secs`.`sec_id`=`art_cats`.`sec_id`
WHERE `art_conts`.`cat_id` = '59'
AND `art_conts`.`cont_status` = 'public'
ORDER BY `cont_id` desc;
2 回答
一个好的开始是在SELECT语句前面使用Keword“EXPLAIN” .
大多数情况下,索引不正确或未使用 .
如果可能,请在语句中避免使用“*”,并仅选择您真正需要的行和列 .
尝试使用art_conts的索引,如下所示: