mod_news_pro_gk4_for_responsive_templates optimize

Hi all,
I use latest version (3.3.8) of mod_news_pro_gk4_for_responsive_templates.
On frontpage installed 5 mod_news_pro_gk4_for_responsive_templates modules.
This modules do HUGE not optimized (joins without indexes) load to MySQL server.
Example query:
# Query_time: 3.610485 Lock_time: 0.000423 Rows_sent: 6676 Rows_examined: 145057
SET timestamp=1396235836;
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.language, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,(
SELECT MAX(contact.id) AS id
FROM j25_contact_details AS contact
WHERE contact.published = 1 AND contact.user_id = a.created_by) as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM j25_content AS a
LEFT JOIN j25_categories AS c ON c.id = a.catid
LEFT JOIN j25_users AS ua ON ua.id = a.created_by
LEFT JOIN j25_users AS uam ON uam.id = a.modified_by
LEFT JOIN j25_categories as parent ON parent.id = c.parent_id
LEFT JOIN j25_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM j25_categories AS cat JOIN j25_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
INNER JOIN j25_content_frontpage AS fp ON fp.content_id = a.id
WHERE a.access IN (1,1) AND c.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-03-31 03:17:13') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-03-31 03:17:13')
ORDER BY c.lft, a.created DESC , a.created DESC;

How to optimize and cache it?

You should use Joomla! cache to avoid multiple queries but the amount of JOIN can't be reduced. Joomla! database has kind of structure which we can't change, to get multiple details about article it is necessary to create multiple JOIN commands.

We use Gavick cche plugin + Joomla cache + XCache.
But we need less usage for CPU and MySQL.

I know that trouble in News Show Pro GK4 code.
Only this modules on my site do this huge joins without indexes.

Yes and there is no other way to avoid join queries with this Joomla database structure when you want to display content with this amount of additional informations which NSP may display. I also don't think that cche plugin + Joomla cache + XCache is quite good solution, you have three different plugins which probably do exactly the same work but three times when every page is cached.

Joomla use XCache as cache device.
You think i must disable gavick cache plugin?

I found workaround.
1. I was replaced all 9 GK 4 modules to GK5 version https://www.gavick.com/download/joomla- ... owpro.html (it have version for 2.5)
2. Set option "Show article created after" from "empty" to 14-180 days (depend on frequency new articles in category).

Look at CPU usage by MySQL:

gk4-g5-optimizatons.png

In digits:
MySQL workload reduced from 150-170 qps to 91-105 qps.

The GK5 module have better optimized queries and the difference is visible mainly in websites with huge amount of data.