flexi scripts casuing table locks and ultimately server fail

hi again Mr. Georgios Papadakis, so we are now running FLEXIcontent version 2.2.0 r1895

and for some reason the scripts are killing us. This is from our host. "...Unfortunately we're not out of the woods here yet as it appears that MySQL is creating the load that is currently being generated on the server.Please contact the developer to further check scripts running on the server causing this."

More form the host
A single query can create a backlog of other queries that can't complete, driving up the loads. the query in question appears to be a variation of the following:

SELECT i.id , ch.hits FROM u49ql_content AS i JOIN u49ql_flexicontent_items_ext AS ie on ie.item_id = i.id JOIN u49ql_flexicontent_types AS ty on ie.type_id = ty.id JOIN u49ql_flexicontent_cats_item_relations AS rel ON rel.itemid = i.id JOIN u49ql_categories AS c ON c.id = rel.catid JOIN u49ql_categories AS mc ON mc.id = i.catid INNER JOIN u49ql_flexicontent_content_hits AS ch ON ch.content_id = i.id AND ch.date > "2014-04-22" WHERE c.published = 1 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2014-04-29 23:24:02' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2014-04-29 23:24:02' ) AND ty.access IN (0,1,1) AND mc.access IN (0,1,1) AND i.access IN (0,1,1) AND i.id <> 0 AND i.state IN ( 1, -5 ) AND c.id IN (52) GROUP BY i.id ORDER BY ch.hits DESC, i.title LIMIT 0, 10;

any suggestions? I will provide you with Cpanel access so you can access the DB and PHP directly.(via PM)

Thanks
Robert

Hello

this is either in category view or in universal module
and it is much lighter than Joomla 's respective query

since it only select id and the ordering column

so there is little to do in improving it, using the joomla 's respective query would make it worse (unless you select to make your queries unordered !!)

so only thing you can do is enable caching:

please backup and test with v2.2.0 r1901:

enable either
- Joomla system cache plugin or
- better try: Joomla conservative caching, and if some modules examine the currently active item, only in them disable caching in their configuration


FLEXIcontent v2.2.0 r1901 BETA for J2.5/J3.x


Regards

Hello

you said that you have 34 FLEXIcontent universal modules in a single page ?