Heavy SQL-queries caused by CP?

I have a problem with my site crashing caused by by heavy SQL-queries. An example from the query shows that its probably caused by custom properties. See the example query my provider sent me.
Joomla 3.4.1, CP 3.1.13. Template: Yoo_eat
Can anyone see what the problem can be? I have no idea, and not the knowledge needed!

SELECT DISTINCT 'content' AS content_element ,c.id AS id,c.title AS title,c.alias AS title_alias,c.catid AS catid ,c.introtext AS introtext,CONCAT('index.php?option=com_content&view=article','&catid=', c.catid,':',cat.alias, '&id=', c.id,':',c.alias) AS hr
ef,c.created AS created,c.`fulltext` AS `fulltext`,c.images AS images,c.ordering AS ordering,cat.title AS category_title ,cat.alias AS cat_alias ,cat.lft AS cat_ordering ,c.publish_up AS publish_up,c.publish_down AS publish_down
FROM swk_content AS c
LEFT JOIN swk_categories AS cat ON(c.catid = cat.id)
INNER JOIN swk_custom_properties AS cp0
ON(c.id = cp0.content_id)
INNER JOIN swk_custom_properties_values AS v0
ON (cp0.value_id = v0.id )
INNER JOIN swk_custom_properties_fields AS f0
ON (v0.field_id = f0.id )
INNER JOIN swk_custom_properties AS cp1
ON(c.id = cp1.content_id)
INNER JOIN swk_custom_properties_values AS v1
ON (cp1.value_id = v1.id )
INNER JOIN swk_custom_properties_fields AS f1
ON (v1.field_id = f1.id )
INNER JOIN swk_custom_properties AS cp1_1
ON(c.id = cp1_1.content_id)
INNER JOIN swk_custom_properties_values AS v1_1
ON (cp1_1.value_id = v1_1.id )
INNER JOIN swk_custom_properties_fields AS f1_1
ON (v1_1.field_id = f1_1.id )

INNER JOIN swk_custom_properties AS cp2
ON(c.id = cp2.content_id)
INNER JOIN swk_custom_properties_values AS v2
ON (cp2.value_id = v2.id )
INNER JOIN swk_custom_properties_fields AS f2
ON (v2.field_id = f2.id )
INNER JOIN swk_custom_properties AS cp3
ON(c.id = cp3.content_id)
INNER JOIN swk_custom_properties_values AS v3
ON (cp3.value_id = v3.id )
INNER JOIN swk_custom_properties_fields AS f3
ON (v3.field_id = f3.id )
INNER JOIN swk_custom_properties AS cp3_1
ON(c.id = cp3_1.content_id)
INNER JOIN swk_custom_properties_values AS v3_1
ON (cp3_1.value_id = v3_1.id )
INNER JOIN swk_custom_properties_fields AS f3_1
ON (v3_1.field_id = f3_1.id )

INNER JOIN swk_custom_properties AS cp3_2
ON(c.id = cp3_2.content_id)
INNER JOIN swk_custom_properties_values AS v3_2
ON (cp3_2.value_id = v3_2.id )
INNER JOIN swk_custom_properties_fields AS f3_2
ON (v3_2.field_id = f3_2.id )

INNER JOIN swk_custom_properties AS cp4
ON(c.id = cp4.content_id)
INNER JOIN swk_custom_properties_values AS v4
ON (cp4.value_id = v4.id )
INNER JOIN swk_custom_properties_fields AS f4
ON (v4.field_id = f4.id )
INNER JOIN swk_custom_properties AS cp5
ON(c.id = cp5.content_id)
INNER JOIN swk_custom_properties_values AS v5
ON (cp5.value_id = v5.id )
INNER JOIN swk_custom_properties_fields AS f5
ON (v5.field_id = f5.id )
WHERE (cat.published = '1' OR cat.published IS NULL)
AND c.state = '1'
AND c.access in (1,1,5)
AND cp0.ref_table = 'content'
AND ((f0.name = 'landskap' AND v0.name = 'uppland' AND f0.published='1' AND f0.access IN (1,1,5) ) OR (f0.name = 'landskap' AND v0.name = 'skne' AND f0.published='1' AND f0.access IN (1,1,5) ))
AND cp1.ref_table = 'content'
AND (f1.name = 'anlggningstyp' AND v1.name = 'lgergrd' AND f1.published='1' AND f1.access IN (1,1,5) )
AND (f1_1.name = 'anlggningstyp' AND v1_1.name = 'grupplogi_fr_sjlvhushll' AND f1_1.published='1' AND f1_1.access IN (1,1,5) )
AND cp2.ref_table = 'content'
AND (f2.name = 'antal_bddar' AND v2.name = '50_bddar' AND f2.published='1' AND f2.access IN (1,1,5) )
AND cp3.ref_table = 'content'
AND (f3.name = 'omgivning' AND v3.name = 'landsbygd' AND f3.published='1' AND f3.access IN (1,1,5) )
AND (f3_1.name = 'omgivning' AND v3_1.name = 'skog' AND f3_1.published='1' AND f3_1.access IN (1,1,5) )
AND (f3_2.name = 'omgivning' AND v3_2.name = 'sj' AND f3_2.published='1' AND f3_2.access IN (1,1,5) )
AND cp4.ref_table = 'content'
AND (f4.name = 'ppet_fr_dropin_boende' AND v4.name = 'augusti' AND f4.published='1' AND f4.access IN (1,1,5) )
AND cp5.ref_table = 'content'
AND (f5.name = 'utrustning' AND v5.name = 'sjlvhushll' AND f5.published='1' AND f5.access IN (1,1,5) )
AND ( c.publish_up = '0000-00-00 00:00:00' OR c.publish_up <= '2015-06-28 18:41:27' )
AND ( c.publish_down = '0000-00-00 00:00:00' OR c.publish_down >= '2015-06-28 18:41:27' )
ORDER BY cat.`lft`,c.title

from: www.solidsystem.it/forum/8-cp-20-for-j16...y-very-slow.html#410

We made quite a few tests up to 10k articles and 20k tags assigned, on a very humble server (year 2007, 1GB RAM), and we've never been able to exceed 1.3 sec rendering included. According to our results search by text is not slower than standard Joomla search nor is slower than regular CP search.
The worst performance are given by a particular combination: all Custom Properties fields as checkboxes, all 20 checkboxes checked.

These ^^ were the results of tests we've made in 2012 with Custom Properties for J 1.5.
Subsequent version of CP had SQL query tweaked and optimized the queries even further and any contemporary server should handle that load easily.

If CP query still fails, it means there are insufficient resources allocated to MySQL server.

OK, thanks, but think I have to give you som more information

This problem occurs every second week or so. Its not a problem all the time, usually it runs fine. But then sometimes the query (according to my provider) hangs for ever and does not end. The memory 1024 Mb gets full and the site crashes. The provider then have to restart everything to get it up running again.
As it only occurs from time to time its really hard to troubleshoot...

I have now upgraded to the latest version of CP 3.1.14, but when upgrading to CP 3.1.13, nothing changed .
Any advice on something to try?

As you're saying, dubugging an issue that happens so infrequently is quite challenging.

I think that the problem could be caused by a concurrency of factors, i.e. someone runs an "heavy query" on your site while, at the same time, another process on the same server (another site ? a scheduled task?) is running another heavy query.

The solution usually involves tweaking and optimizing MySQL settings. In our experience increasing key_buffer_size and table_cache params is ususally helpful.

Hello again. Still searching for a solution for this, and can give some more information. I now have my site offline testing and can see that running a search in CP with quite a few options selected hangs the query. Also tested to run the same query in phpMyAdmin, the server times out with no response.

The query that hangs the server is in the bypassed file, where the large number of chosen options in "AND (f2_1.name = 'antal_bddar'" seems to be the problem. If reducing the number chosen the query runs well.

Is there a way to limit the possibility to choose options, adding some index or do you have any other suggestion to solve this?

Sorry can not succeed attaching the query...

SELECT DISTINCT 'content' AS content_element ,c.id AS id,c.title AS title,c.alias AS title_alias,c.catid AS catid ,c.introtext AS introtext,CONCAT('index.php? option=com_content&view=article','&catid=', c.catid,':'
,cat.alias, '&id=', c.id,':',c.alias) AS href,c.created AS created,c.`fulltext` AS `fulltext`,c.images AS images,c.ordering AS ordering,cat.title AS
category_title ,cat.alias AS cat_alias ,cat.lft AS cat_ordering ,c.publish_up AS publish_up,c.publish_down AS publish_down
FROM swk_content AS c
LEFT JOIN swk_categories AS cat ON(c.catid = cat.id)
INNER JOIN swk_custom_properties AS cp0 ON(c.id = cp0.content_id)
INNER JOIN swk_custom_properties_values AS v0 ON (cp0.value_id = v0.id )
INNER JOIN swk_custom_properties_fields AS f0 ON (v0.field_id = f0.id )
INNER JOIN swk_custom_properties AS cp1 ON(c.id = cp1.content_id)
INNER JOIN swk_custom_properties_values AS v1 ON (cp1.value_id = v1.id )
INNER JOIN swk_custom_properties_fields AS f1 ON (v1.field_id = f1.id )
INNER JOIN swk_custom_properties AS cp1_1 ON(c.id = cp1_1.content_id)
INNER JOIN swk_custom_properties_values AS v1_1 ON (cp1_1.value_id = v1_1.id )
INNER JOIN swk_custom_properties_fields AS f1_1 ON (v1_1.field_id = f1_1.id )
INNER JOIN swk_custom_properties AS cp2 ON(c.id = cp2.content_id)
INNER JOIN swk_custom_properties_values AS v2 ON (cp2.value_id = v2.id )
INNER JOIN swk_custom_properties_fields AS f2 ON (v2.field_id = f2.id )
INNER JOIN swk_custom_properties AS cp2_1 ON(c.id = cp2_1.content_id)
INNER JOIN swk_custom_properties_values AS v2_1 ON (cp2_1.value_id = v2_1.id )
INNER JOIN swk_custom_properties_fields AS f2_1 ON (v2_1.field_id = f2_1.id )
INNER JOIN swk_custom_properties AS cp2_2 ON(c.id = cp2_2.content_id)
INNER JOIN swk_custom_properties_values AS v2_2 ON (cp2_2.value_id = v2_2.id )
INNER JOIN swk_custom_properties_fields AS f2_2 ON (v2_2.field_id = f2_2.id )
INNER JOIN swk_custom_properties AS cp2_3 ON(c.id = cp2_3.content_id)
INNER JOIN swk_custom_properties_values AS v2_3 ON (cp2_3.value_id = v2_3.id )
INNER JOIN swk_custom_properties_fields AS f2_3 ON (v2_3.field_id = f2_3.id )
INNER JOIN swk_custom_properties AS cp2_4 ON(c.id = cp2_4.content_id)
INNER JOIN swk_custom_properties_values AS v2_4 ON (cp2_4.value_id = v2_4.id )
INNER JOIN swk_custom_properties_fields AS f2_4 ON (v2_4.field_id = f2_4.id )
INNER JOIN swk_custom_properties AS cp2_5 ON(c.id = cp2_5.content_id)
INNER JOIN swk_custom_properties_values AS v2_5 ON (cp2_5.value_id = v2_5.id )
INNER JOIN swk_custom_properties_fields AS f2_5 ON (v2_5.field_id = f2_5.id )
INNER JOIN swk_custom_properties AS cp2_6 ON(c.id = cp2_6.content_id)
INNER JOIN swk_custom_properties_values AS v2_6 ON (cp2_6.value_id = v2_6.id )
INNER JOIN swk_custom_properties_fields AS f2_6 ON (v2_6.field_id = f2_6.id )
INNER JOIN swk_custom_properties AS cp3 ON(c.id = cp3.content_id)
INNER JOIN swk_custom_properties_values AS v3 ON (cp3.value_id = v3.id )
INNER JOIN swk_custom_properties_fields AS f3 ON (v3.field_id = f3.id )
INNER JOIN swk_custom_properties AS cp3_1 ON(c.id = cp3_1.content_id)
INNER JOIN swk_custom_properties_values AS v3_1 ON (cp3_1.value_id = v3_1.id )
INNER JOIN swk_custom_properties_fields AS f3_1 ON (v3_1.field_id = f3_1.id )
WHERE (cat.published = '1' OR cat.published IS NULL) AND c.state = '1'
AND c.access in (1,1,5)
AND cp0.ref_table = 'content'
AND ((f0.name = 'landskap' AND v0.name = 'stockholm' AND f0.published='1' AND f0.access IN (1,1,5) ))
AND cp1.ref_table = 'content'
AND (f1.name = 'anlggningstyp' AND v1.name = 'kursgrd' AND f1.published='1' AND f1.access IN (1,1,5) )
AND (f1_1.name = 'anlggningstyp' AND v1_1.name = 'lgergrd' AND f1_1.published='1' AND f1_1.access IN (1,1,5) )
AND cp2.ref_table = 'content'
AND (f2.name = 'antal_bddar' AND v2.name = '20_bddar' AND f2.published='1' AND f2.access IN (1,1,5) )
AND (f2_1.name = 'antal_bddar' AND v2_1.name = '30_bddar' AND f2_1.published='1' AND f2_1.access IN (1,1,5) )
AND (f2_2.name = 'antal_bddar' AND v2_2.name = '40_bddar' AND f2_2.published='1' AND f2_2.access IN (1,1,5) )
AND (f2_3.name = 'antal_bddar' AND v2_3.name = '50_bddar' AND f2_3.published='1' AND f2_3.access IN (1,1,5) )
AND (f2_4.name = 'antal_bddar' AND v2_4.name = '60_bddar' AND f2_4.published='1' AND f2_4.access IN (1,1,5) )
AND (f2_5.name = 'antal_bddar' AND v2_5.name = '70_bddar' AND f2_5.published='1' AND f2_5.access IN (1,1,5) )
AND (f2_6.name = 'antal_bddar' AND v2_6.name = '80_bddar' AND f2_6.published='1' AND f2_6.access IN (1,1,5) )
AND cp3.ref_table = 'content'
AND (f3.name = 'omgivning' AND v3.name = 'sj' AND f3.published='1' AND f3.access IN (1,1,5) )
AND (f3_1.name = 'omgivning' AND v3_1.name = 'hav' AND f3_1.published='1' AND f3_1.access IN (1,1,5) )
AND ( c.publish_up = '0000-00-00 00:00:00' OR c.publish_up <= '2015-05-21 12:30:11' )
AND ( c.publish_down = '0000-00-00 00:00:00' OR c.publish_down >= '2015-05-21 12:30:11' )
ORDER BY cat.`lft`,c.title