Query taking too long

Hi I have problem with mysql database. Some query turning down my database. So I receive a report from my webhost provider. Can you please look and resolve this?

---

Command: Query

Time: 177

State: statistics

Info: 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 ,cat.alias AS cat_alias ,cat.lft AS cat_ordering ,c.publish_up AS publish_up,c.publish_down AS publish_down FROM j25_content AS c LEFT JOIN j25_categories AS cat ON(c.catid = cat.id) INNER JOIN j25_custom_properties AS cp0

ON(c.id = cp0.content_id)

INNER JOIN j25_custom_properties_values AS v0

ON (cp0.value_id = v0.id )

INNER JOIN j25_custom_properties_fields AS f0

ON (v0.field_id = f0.id )

INNER JOIN j25_custom_properties AS cp1

ON(c.id = cp1.content_id)

INNER JOIN j25_custom_properties_values AS v1

ON (cp1.value_id = v1.id )

INNER JOIN j25_custom_properties_fields AS f1

ON (v1.field_id = f1.id )

INNER JOIN j25_custom_properties AS cp2

ON(c.id = cp2.content_id)

INNER JOIN j25_custom_properties_values AS v2

ON (cp2.value_id = v2.id )

INNER JOIN j25_custom_properties_fields AS f2

ON (v2.field_id = f2.id )

INNER JOIN j25_custom_properties AS cp3

ON(c.id = cp3.content_id)

INNER JOIN j25_custom_properties_values AS v3

ON (cp3.value_id = v3.id )

INNER JOIN j25_custom_properties_fields AS f3

ON (v3.field_id = f3.id )

INNER JOIN j25_custom_properties AS cp4

ON(c.id = cp4.content_id)

INNER JOIN j25_custom_properties_values AS v4

ON (cp4.value_id = v4.id )

INNER JOIN j25_custom_properties_fields AS f4

ON (v4.field_id = f4.id )

INNER JOIN j25_custom_properties AS cp5

ON(c.id = cp5.content_id)

INNER JOIN j25_custom_properties_values AS v5

ON (cp5.value_id = v5.id )

INNER JOIN j25_custom_properties_fields AS f5

ON (v5.field_id = f5.id )

INNER JOIN j25_custom_properties AS cp6

ON(c.id = cp6.content_id)

INNER JOIN j25_custom_properties_values AS v6

ON (cp6.value_id = v6.id )

INNER JOIN j25_custom_properties_fields AS f6

ON (v6.field_id = f6.id )

INNER JOIN j25_custom_properties AS cp7

ON(c.id = cp7.content_id)

INNER JOIN j25_custom_properties_values AS v7

ON (cp7.value_id = v7.id )

INNER JOIN j25_custom_properties_fields AS f7

ON (v7.field_id = f7.id )

INNER JOIN j25_custom_properties AS cp8

ON(c.id = cp8.content_id)

INNER JOIN j25_custom_properties_values AS v8

ON (cp8.value_id = v8.id )

INNER JOIN j25_custom_properties_fields AS f8

ON (v8.field_id = f8.id )

INNER JOIN j25_custom_properties AS cp9

ON(c.id = cp9.content_id)

INNER JOIN j25_custom_properties_values AS v9

ON (cp9.value_id = v9.id )

INNER JOIN j25_custom_properties_fields AS f9

ON (v9.field_id = f9.id )

INNER JOIN j25_custom_properties AS cp10

ON(c.id = cp10.content_id)

INNER JOIN j25_custom_properties_values AS v10

ON (cp10.value_id = v10.id )

INNER JOIN j25_custom_properties_fields AS f10

ON (v10.field_id = f10.id )

WHERE (cat.published = '1' OR cat.published IS NULL) AND c.state = '1'

AND c.access in (1,1,8,9)

AND cp0.ref_table = 'content'

AND ((f0.name = 'izkusenost_lastnika' AND v0.name = 'prvic_lastnik' AND f0.published='1' AND f0.access IN (1,1,8,9) )) AND cp1.ref_table = 'content'

AND ((f1.name = 'kako_velikega_psa_si_zelite' AND v1.name = 'zelo_majhnega' AND f1.published='1' AND f1.access IN (1,1,8,9) )) AND cp2.ref_table = 'content'

AND ((f2.name = 'tip_dlake_pri_psih' AND v2.name = 'dolga' AND f2.published='1' AND f2.access IN (1,1,8,9) ) OR (f2.name = 'tip_dlake_pri_psih' AND v2.name = 'srednje_dolga_dlaka' AND f2.published='1' AND f2.access IN (1,1,8,9) )) AND cp3.ref_table = 'content'

AND ((f3.name = 'vas_moti_odpadanje_dlak' AND v3.name = 'ne' AND f3.published='1' AND f3.access IN (1,1,8,9) )) AND cp4.ref_table = 'content'

AND ((f4.name = 'koliko_casa_ste_pripravljeni_posvetiti_negi' AND v4.name = 'srednje' AND f4.published='1' AND f4.access IN (1,1,8,9) ) OR (f4.name = 'koliko_casa_ste_pripravljeni_posvetiti_negi' AND v4.name = 'veliko' AND f4.published='1' AND f4.access IN (1,1,8,9) )) AND cp5.ref_table = 'content'

AND ((f5.name = 'ste_astmatik_ali_alergik_iscete_hipoalergenega_psa' AND v5.name = 'ne' AND f5.published='1' AND f5.access IN (1,1,8,9) )) AND cp6.ref_table = 'content'

AND ((f6.name = 'iz_katerega_razloga_si_zelite_psa' AND v6.name = 'pes_za_druzbo' AND f6.published='1' AND f6.access IN (1,1,8,9) ) OR (f6.name = 'iz_katerega_razloga_si_zelite_psa' AND v6.name = 'prijatelj_v_druzini_z_otroki' AND f6.published='1' AND f6.access IN (1,1,8,9) )) AND cp7.ref_table = 'content'

AND ((f7.name = 'ste_bolj_ali_manj_portno_aktivni_in_hodite_v_n' AND v7.name = 'srednje' AND f7.published='1' AND f7.access IN (1,1,8,9) )) AND cp8.ref_table = 'content'

AND ((f8.name = 'na_kako_zahtevno_pasmo_ste_pripravljeni' AND v8.name = 'nezahtevno' AND f8.published='1' AND f8.access IN (1,1,8,9) )) AND cp9.ref_table = 'content'

AND ((f9.name = 'kje_bo_pes_prenoceval' AND v9.name = 'notri' AND f9.published='1' AND f9.access IN (1,1,8,9) )) AND cp10.ref_table = 'content'

AND ((f10.name = 'ze_imate_hisnega_ljubljencka' AND v10.name = 'da' AND f10.published='1' AND f10.access IN (1,1,8,9) )) AND ( c.publish_up = '0000-00-00 00:00:00' OR c.publish_up <= '2013-03-07 13:28:04' ) AND ( c.publish_down = '0000-00-00 00:00:00' OR c.publish_down >= '2013-03-07 13:28:04' ) AND c.catid IN (9) ORDER BY c.title

[2]


Time: 141

State: statistics

Info: 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 ,cat.alias AS cat_alias ,cat.lft AS cat_ordering ,c.publish_up AS publish_up,c.publish_down AS publish_down

FROM j25_content AS c

LEFT JOIN j25_categories AS cat ON(c.catid = cat.id)

INNER JOIN j25_custom_properties AS cp0

ON(c.id = cp0.content_id)

INNER JOIN j25_custom_properties_values AS v0

ON (cp0.value_id = v0.id )

INNER JOIN j25_custom_properties_fields AS f0

ON (v0.field_id = f0.id )

INNER JOIN j25_custom_properties AS cp1

ON(c.id = cp1.content_id)

INNER JOIN j25_custom_properties_values AS v1

ON (cp1.value_id = v1.id )

INNER JOIN j25_custom_properties_fields AS f1

ON (v1.field_id = f1.id )

INNER JOIN j25_custom_properties AS cp2

ON(c.id = cp2.content_id)

INNER JOIN j25_custom_properties_values AS v2

ON (cp2.value_id = v2.id )

INNER JOIN j25_custom_properties_fields AS f2

ON (v2.field_id = f2.id )

INNER JOIN j25_custom_properties AS cp3

ON(c.id = cp3.content_id)

INNER JOIN j25_custom_properties_values AS v3

ON (cp3.value_id = v3.id )

INNER JOIN j25_custom_properties_fields AS f3

ON (v3.field_id = f3.id )

INNER JOIN j25_custom_properties AS cp4

ON(c.id = cp4.content_id)

INNER JOIN j25_custom_properties_values AS v4

ON (cp4.value_id = v4.id )

INNER JOIN j25_custom_properties_fields AS f4

ON (v4.field_id = f4.id )

INNER JOIN j25_custom_properties AS cp5

ON(c.id = cp5.content_id)

INNER JOIN j25_custom_properties_values AS v5

ON (cp5.value_id = v5.id )

INNER JOIN j25_custom_properties_fields AS f5

ON (v5.field_id = f5.id )

INNER JOIN j25_custom_properties AS cp6

ON(c.id = cp6.content_id)

INNER JOIN j25_custom_properties_values AS v6

ON (cp6.value_id = v6.id )

INNER JOIN j25_custom_properties_fields AS f6

ON (v6.field_id = f6.id )

INNER JOIN j25_custom_properties AS cp7

ON(c.id = cp7.content_id)

INNER JOIN j25_custom_properties_values AS v7

ON (cp7.value_id = v7.id )

INNER JOIN j25_custom_properties_fields AS f7

ON (v7.field_id = f7.id )

INNER JOIN j25_custom_properties AS cp8

ON(c.id = cp8.content_id)

INNER JOIN j25_custom_properties_values AS v8

ON (cp8.value_id = v8.id )

INNER JOIN j25_custom_properties_fields AS f8

ON (v8.field_id = f8.id )

INNER JOIN j25_custom_properties AS cp9

ON(c.id = cp9.content_id)

INNER JOIN j25_custom_properties_values AS v9

ON (cp9.value_id = v9.id )

INNER JOIN j25_custom_properties_fields AS f9

ON (v9.field_id = f9.id )

INNER JOIN j25_custom_properties AS cp10

ON(c.id = cp10.content_id)

INNER JOIN j25_custom_properties_values AS v10

ON (cp10.value_id = v10.id )

INNER JOIN j25_custom_properties_fields AS f10

ON (v10.field_id = f10.id )

WHERE (cat.published = '1' OR cat.published IS NULL)

AND c.state = '1'

AND c.access in (1,1,8,9)

AND cp0.ref_table = 'content'

AND ((f0.name = 'izkusenost_lastnika' AND v0.name = 'izkusen_lastnik' AND f0.published='1' AND f0.access IN (1,1,8,9) ))

AND cp1.ref_table = 'content'

AND ((f1.name = 'kako_velikega_psa_si_zelite' AND v1.name = 'velikega' AND f1.published='1' AND f1.access IN (1,1,8,9) ))

AND cp2.ref_table = 'content'

AND ((f2.name = 'tip_dlake_pri_psih' AND v2.name = 'kratka_dlaka' AND f2.published='1' AND f2.access IN (1,1,8,9) ))

AND cp3.ref_table = 'content'

AND ((f3.name = 'vas_moti_odpadanje_dlak' AND v3.name = 'ne' AND f3.published='1' AND f3.access IN (1,1,8,9) ))

AND cp4.ref_table = 'content'

AND ((f4.name = 'koliko_casa_ste_pripravljeni_posvetiti_negi' AND v4.name = 'vseeno' AND f4.published='1' AND f4.access IN (1,1,8,9) ))

AND cp5.ref_table = 'content'

AND ((f5.name = 'ste_astmatik_ali_alergik_iscete_hipoalergenega_psa' AND v5.name = 'ne' AND f5.published='1' AND f5.access IN (1,1,8,9) ))

AND cp6.ref_table = 'content'

AND ((f6.name = 'iz_katerega_razloga_si_zelite_psa' AND v6.name = 'prijatelj_v_druzini_z_otroki' AND f6.published='1' AND f6.access IN (1,1,8,9) ) OR (f6.name = 'iz_katerega_razloga_si_zelite_psa' AND v6.name = 'delovni_pes' AND f6.published='1' AND f6.access IN (1,1,8,9) ))

AND cp7.ref_table = 'content'

AND ((f7.name = 'ste_bolj_ali_manj_portno_aktivni_in_hodite_v_n' AND v7.name = 'srednje' AND f7.published='1' AND f7.access IN (1,1,8,9) ))

AND cp8.ref_table = 'content'

AND ((f8.name = 'na_kako_zahtevno_pasmo_ste_pripravljeni' AND v8.name = 'srednje_zahtevno' AND f8.published='1' AND f8.access IN (1,1,8,9) ))

AND cp9.ref_table = 'content'

AND ((f9.name = 'kje_bo_pes_prenoceval' AND v9.name = 'notri_zunaj' AND f9.published='1' AND f9.access IN (1,1,8,9) ))

AND cp10.ref_table = 'content'

AND ((f10.name = 'ze_imate_hisnega_ljubljencka' AND v10.name = 'ne' AND f10.published='1' AND f10.access IN (1,1,8,9) ))

AND ( c.publish_up = '0000-00-00 00:00:00' OR c.publish_up <= '2013-03-06 18:00:40' )

AND ( c.publish_down = '0000-00-00 00:00:00' OR c.publish_down >= '2013-03-06 18:00:40' )

AND c.catid IN (9)

ORDER BY c.title

According to the query you posted, the search function on your site has many criteria (at least 11).
There's no way to make the query lighter, so you either need to remove some criteria or you have to ask your hoster for more resources for MySql.

See this earlier discussion about the same topic.
www.solidsystem.it/forum/10-cp-20-for-j1...ading-time.html#1228

Hi again,

is it possible to restrict search form so that user can select only 5 of 11 criteria?

There's no such possibility.
A custom JS validation function should be developed and put in place, to accomplish that.

Hi again, where do I put this code into which file?

A suitable place would be: modules\mod_cpsearch\js\cp_search.js