Godaddy said that MySQL database caused an overload of shared resources for the server that it resides on

They email me that 

Your sql1322912284564 MySQL database caused an overload of shared resources for the server that it resides on. This overload threatened the health of the server and, in turn, threatened the connectivity of other customers utilizing it. This is, of course, unacceptable. Our database administrators had no choice but to take immediate action to protect our network and to prevent the possibility of a service interruption for other customers. Your database was disabled as a result. 

As we are unable to troubleshoot custom scripting, we are unable to provide a specific solution to you. However the following information was provided by our administrators: 

Problematic query: 
SELECT count(DISTINCT e.guid) as total FROM elgg_entities e JOIN elgg_metadata n_table on 
e.guid = n_table.entity_guid JOIN elgg_metastrings msn on n_table.name_id = msn.id JOIN elgg_users_entity ue ON e.guid = ue.guid JOIN elgg_metadata md on e.guid = md.entity_guid JOIN elgg_metastrings msv ON n_table.value_id = msv.id WHERE (((MATCH (ue.username,ue.name) AGAINST ('+Tischlerei' IN BOOLEAN MODE))) OR ((((((msn.string IN ('description','briefdescription','location','interests','skills','contactemail','phone','mobile','website','twitter')) AND ( (n_table.access_id IN (2) 
OR (n_table.owner_guid = -1) 
OR ( 
n_table.access_id = 0 
AND n_table.owner_guid = -1 

) and n_table.enabled='yes')))) AND msv.string LIKE '%Tischlerei%'))) AND ((e.type = 'user')) AND (e.site_guid IN (1)) AND ( (e.access_id IN (2) 
OR (e.owner_guid = -1) 
OR ( 
e.access_id = 0 
AND e.owner_guid = -1 

) and e.enabled='yes') 

EXPLAIN: 
id select_type table type possible_keys key key_len ref rows Extra 
1 SIMPLE ue ALL PRIMARY 225807 
1 SIMPLE e eq_ref PRIMARY,type,owner_guid,site_guid,access_id PRIMARY 8 sql1322912284564.ue.guid 1 Using where 
1 SIMPLE n_table ref entity_guid,name_id,value_id,owner_guid,access_id entity_guid 8 sql1322912284564.ue.guid 4 Using where 
1 SIMPLE msn eq_ref PRIMARY,string PRIMARY 4 sql1322912284564.n_table.name_id 1 Using where 
1 SIMPLE msv eq_ref PRIMARY PRIMARY 4 sql1322912284564.n_table.value_id 1 Using where 
1 SIMPLE md ref entity_guid entity_guid 8 sql1322912284564.ue.guid 4 Using index 

This query examines 3612912 rows, which is unacceptable in shared hosting.
 

I had try to find that query in /engine/lib/entities.php, /engine/lib/metadata.php and  /engine/lib/metastring.php. But i don't know how to fix thiat problems. Does anyone had the same problem with me and can you show me the way to fix that. Please help me!

  • you shouldn't touch those valuable php files. you see, shared hosting is very limited and can't handle elgg's load. my best advice to you is to upgrade servers either to a vps or dedicated

  • You might not like what I say...

    Shared hosting is not really suitable for Elgg. If you are lucky, the load on the server will stay below the restrictions defined by the hoster. But it could easily happen that the load gets too high. It not only depends on the number of members of your site but also on the number of members online at the same time and additionally on the plugins used. For example a chat plugin could cause too much load quite easily but others might result in a high load also even if you wouldn't believe this at first.

    The query above seems caused by a search on the term "Tischlerei". The search does also look in the profile fields for a match. Someone searching for something seems not an unlikely situation. You could disable the search plugin to avoid such a search to happen again. Still, on a shared server you might get in the same situation with your site causing too much load on the server sooner or later again. My advice: change to a VPS hosting plan.