Slow query issue!

Question

Maybe someone can help me with this issue. My site has a slow query:

SELECT DISTINCT i.*, g.title AS groupname, c.name AS category, v.name AS author, w.name as moderator, u.name AS editor 
FROM p38xt_k2_items as i 
LEFT JOIN p38xt_k2_categories AS c ON c.id = i.catid 
LEFT JOIN p38xt_viewlevels AS g ON g.id = i.access 
LEFT JOIN p38xt_users AS u ON u.id = i.checked_out 
LEFT JOIN p38xt_users AS v ON v.id = i.created_by 
LEFT JOIN p38xt_users AS w ON w.id = i.modified_by 
WHERE i.trash=0 ORDER BY i.id DESC LIMIT 0, 50;

I have checked every module that does k2 feeds, but was unable to find the 50 limit. I also opened all .php of the /modules/* but even there I could not find: SELECT DISTINCT i.*, g.title

Does someone know were this query might be generated?! Is there any Joomla Extension that can track the queries executed from the modules?!

Solution: The issue of this problem was the K2 multicategory plugin that I was using on the site!

Answer 1

Enable the system debug in Global Configuration/System.

Open a page using k2 and in the Joomla! Debug Console under Database Queries you will find every query executed on that page with execution time, used memory and a Call Stack that identifies every file involved.

---

You can also change allowed user groups and other options of System - Debug plugin under Extensions > Plugin to customize the debug console.

UPDATE: after some research the query was actually executed in backend area by latest k2 items module.

Answer 2

Use EXPLAIN and check which column have more data and taking time and which field require indexing and then apply it and check speed.

EXPLAIN DISTINCT i.*, g.title AS groupname, c.name AS category, v.name AS author, w.name as moderator, u.name AS editor 
FROM p38xt_k2_items as i 
LEFT JOIN p38xt_k2_categories AS c ON c.id = i.catid 
LEFT JOIN p38xt_viewlevels AS g ON g.id = i.access 
LEFT JOIN p38xt_users AS u ON u.id = i.checked_out 
LEFT JOIN p38xt_users AS v ON v.id = i.created_by 
LEFT JOIN p38xt_users AS w ON w.id = i.modified_by 
WHERE i.trash=0 ORDER BY i.id DESC LIMIT 0, 50;