is our site to large for flexicontent

Hi there George,hope all is well! here our question for you, we have been using flexi now for about a year and all was working well until about 2 weeks ago.

we are currently using ver 2.1.0 r1781

lately we running in problems like the one below that is causing the db writes to be prevented
We greatly appreciate your help in getting this resolved


| 546860 | uigvtyaz_racerne | racermcs001.directrouter.com:45973 | uigvtyaz_racernew | Query | 0 | Copying to tmp table | SELECT i.*,ie.*,ty.name AS typename, ch.hits, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':',i.id,i.alias) ELSE i.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':',c.id,c.alias) ELSE c.id END as categoryslug, GROUP_CONCAT(rel.catid SEPARATOR ",") as itemcats 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-18" WHERE i.id IN (102948,102999,102964,103014,103000,103016,102963,102988,102942,102970) GROUP BY i.id LIMIT 0, 10 |
| 546864 | uigvtyaz_racerne | racermcs001.directrouter.com:45988 | uigvtyaz_racernew | Sleep | 0 | | NULL


And

| 546866 | uigvtyaz_racerne | racermcs001.directrouter.com:45993 | uigvtyaz_racernew | Query | 0 | Copying to tmp table | 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-18" WHERE c.published = 1 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2014-04-25 19:23:52' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2014-04-25 19:23:52' ) 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 |

Hello

your version is too old, does not compare in terms of performance with v2.2.0 BETA

Here is a test-run on a popular news web-site running on FC v2.2.0 r1895 BETA

Listing 13000 items (include sub-categories)
Component times
Joomla J2.5.19 : 27 seconds
FC v2.2.0 r1895 : 0.6 seconds

(performance difference in Joomla 3.2 is smaller)

both above have ON:
- include subcategories
- count sub-category items
and OFF:
text-search (Joomla does not have text-search in category view anyway)
FACETED-filtering (Joomla does not have this anyway)


but besides category view many aspects of FC performance are improved, including installation in large web-sites, (tested installation on an 250,000 items web-site)


the version that you mention is too old and we will not support it,

so before we examine your issue you will need to update to newer version

if you want to test now with v2.2.0 r1895 BETA:

please backup the web-site and upgrade to:
FLEXIcontent v2.2.0 r1895 BETA for J2.5/J3.x

or test in a duplicate web-site

1. Your frontend CPU usage will be minimized
2. since the new queries last a lot less there should not be a problem with TABLE LOCKING and DB-writes
3. The new version should be able to work with Joomla progressive caching too, thus reduce the number of SELECT queries on DB tables
4. You may want to backup DB and convert the the tables from MyISAM to InnoDB which does not lock full tables (frontend queries will be about 10%-20% slower than MyISAM but it should not be noticable the new version has SQL queries that are multiple times faster than v2.1.0)

again backup web-site and or test in a development server / or copy web-site

Regards

Thanks George I will get on it pronto!