Thread: MYSQL Problem - Help Needed!

MYSQL Problem - Help Needed!

Hello,

My classipress site goes down regularly. I asked my hosting provider the reason why that is happening and below is what they told me:

While testing the sites, we noticed [ http://www.MYSITE.com/ ] was down, after restarting the MySQL service, this site has been restored.

Upon reviewing a previous ticket, we noticed there was an issue with a MySQL query that was causing MySQL service to stop responding:

----------------------------------------------------------------
There were two queries on your server that tied up all MySQL operations for the entire server:

| 401577 | admin_ssdaireuse | localhost | admin_ssdaireler | Query | 11064 | statistics | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
| 401578 | admin_ssdaireuse | localhost | admin_ssdaireler | Query | 11046 | statistics | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts |
----------------------------------------------------------------

Since this query appears to be cut off, we examined the logs and noticed this is the full query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)
INNER JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id)
INNER JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id)
INNER JOIN wp_postmeta AS mt7 ON (wp_posts.ID = mt7.post_id)
INNER JOIN wp_postmeta AS mt8 ON (wp_posts.ID = mt8.post_id)
INNER JOIN wp_postmeta AS mt9 ON (wp_posts.ID = mt9.post_id)
INNER JOIN wp_postmeta AS mt10 ON (wp_posts.ID = mt10.post_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (90,92,103,105,112,114,116,117,118,121,123,125,126 ,127,1031,1035,1072,1075,1076,1077,1078,1079,1080, 1081,1082,1083,1084,1085,1086,1087,1088,1089,1090, 1091,1092,1093,1094,1095,1096) ) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'ad_listing') AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'cp_price' AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '36000' AND '200000')
AND (mt1.meta_key = 'cp_oda_says' AND CAST(mt1.meta_value AS CHAR) IN ('2+1'))
AND (mt2.meta_key = 'cp_banyo_says' AND CAST(mt2.meta_value AS CHAR) IN ('1'))
AND (mt3.meta_key = 'cp_bina_ya' AND CAST(mt3.meta_value AS CHAR) IN ('10'))
AND (mt4.meta_key = 'cp_binadaki_kat_says' AND CAST(mt4.meta_value AS CHAR) IN ('1'))
AND (mt5.meta_key = 'cp_bulunduu_kat' AND CAST(mt5.meta_value AS CHAR) IN ('Kot 1'))
AND (mt6.meta_key = 'cp_yakt_tipi' AND CAST(mt6.meta_value AS CHAR) IN ('Doğalgaz (Kombi)'))
AND (mt7.meta_key = 'cp_tapu_tr' AND CAST(mt7.meta_value AS CHAR) IN ('Kat İrtifaklı Tapu'))
AND (mt8.meta_key = 'cp_krediye_uygun' AND CAST(mt8.meta_value AS CHAR) IN ('Evet'))
AND (mt9.meta_key = 'cp_manzara_zellikleri' AND CAST(mt9.meta_value AS CHAR) IN ('Şehir'))
AND (mt10.meta_key = 'cp_konut_tipi' AND CAST(mt10.meta_value AS CHAR) IN ('Müstakil Girişli')) ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

We have determined that this query is causing MySQL to stop responding. When we tried to run a EXPLAIN on the query, it was unable to complete in 5 minutes. When more than one instance is ran, it causes MySQL to hang which prevents new queries from running.

In order to resolve this issue, this query will need to be optimized.


I am not a technical person, therefore, would appreciate if anyone can tell me what to do to resolve this problem.

Kind Regards