Hello,
I my website I am getting some slow queries, I was able to remove most of them.
Now I am stuck in this:
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 searched all the modules content but found no where this query.
Could you help me if this is a native k2 query, or give me a info where it came from.

Thank you

Hi,
What is your K2 version ?
- Also if you have an IDE with an option to search a text string in a whole project you can easily find the query you mentioned.
- Check also grep command or grep software.

Hello,
I am using k2 2.6.9, tried searching all the .php files nothing there.

Contacted the New Show Pro Gk5 but they told me that the DISTINCT
command was not in use by their module.

Now I just has two other modules, but seeing their source code and module config can't find nothing
extensions.joomla.org/extension/news-show-sp2
www.joomshaper.com/joomla-extensions/sp-k2-featured-slider

I am afraid that maybe the tag or latest comment of k2 its causing this,
but as I dont know for sure can't make test as the site its online.

Do not search for the whole query. Make a search for SELECT DISTINCT i.*

prntscr.com/as5zny
prntscr.com/as5zwa

I investigated the issue with the Joomla debug feature and guess what the issue was generated
right in the K2 items menu!!! A place not expected from me at all.

Could someone help me how to avoid this issue?!

For the moment I am setting the limit for 50 latest articles to 10,
but still I want to optimize this query.

As the attach feature its not working in this forum I am sending the dropbox link
of the debug output www.dropbox.com/s/l0en8dwrzle4wqy/administrator.html?dl=0
NOTE!!! Open the dropbox file with Notepadd++ to see the entire log!

- Do you mean that the query is executed at the create new K2 item menu at backend ?
- What is the execution time of the query ?
- A good start for query optimization is to execute it with the EXPLAIN keyword in order to check if the appropriate indexes are being setup.

Yes the query its executed there.
Normally the query takes 2.6seconds to execute, but in some cases it takes more than 60seconds.
Two days ago even apache halted from this issue.

I have attached before the link when you can see the queries executed under index.php?option=com_k2&view=items

Please open that file with Notepadd++ so you can see the content!!!
www.dropbox.com/s/l0en8dwrzle4wqy/administrator.html?dl=0

Hello again
Seems that the slow query issue persist in my website.

The site went down again and the reply of siteground was that slow queries are the fault.
I am providing their result, the first 3 one appears in administrator/index.php?option=com_k2&view=items
the next three are generated from the tags module.

Could someone helps me at least fixing the query generated in backend?!

=== Databases Info ====================================================================
Database Tables Views InnoDB MyISAM Slow Queries Slowest Query DB Size

---

---

---

---

---

---

---

---

newsbomb_site 96 0 9 85 4021 9.982 390.2 MB


=== TOP 10 of 4021 (total) Slow Queries for the past 24 hours ==========

1. Executed 2h 52m 51s ago for 23.016417 sec on Database --> newsbomb_site
Date: 2016-09-13 05:54:28 Query_time: 23.016417 Rows_examined: 282137: Rows_sent 20 Lock_time: 0.000120
SELECT DISTINCT i.*, g.title AS groupname, c.name AS category, v.name AS author, w.name as moderator, u.name AS editor FROM site_k2_items as i LEFT JOIN site_k2_categories AS c ON c.id = i.catid LEFT JOIN site_viewlevels AS g ON g.id = i.access LEFT JOIN site_users AS u ON u.id = i.checked_out LEFT JOIN site_users AS v ON v.id = i.created_by LEFT JOIN site_users AS w ON w.id = i.modified_by WHERE i.trash=0 ORDER BY i.id DESC LIMIT 0, 20;

---

2. Executed 3h 7m 44s ago for 18.48233 sec on Database --> newsbomb_site
Date: 2016-09-13 05:39:35 Query_time: 18.482330 Rows_examined: 282124: Rows_sent 20 Lock_time: 0.000138
SELECT DISTINCT i.*, g.title AS groupname, c.name AS category, v.name AS author, w.name as moderator, u.name AS editor FROM site_k2_items as i LEFT JOIN site_k2_categories AS c ON c.id = i.catid LEFT JOIN site_viewlevels AS g ON g.id = i.access LEFT JOIN site_users AS u ON u.id = i.checked_out LEFT JOIN site_users AS v ON v.id = i.created_by LEFT JOIN site_users AS w ON w.id = i.modified_by WHERE i.trash=0 ORDER BY i.id DESC LIMIT 0, 20;

---

3. Executed 5h 1m 37s ago for 10.898614 sec on Database --> newsbomb_site
Date: 2016-09-13 03:45:42 Query_time: 10.898614 Rows_examined: 282036: Rows_sent 20 Lock_time: 0.000118
SELECT DISTINCT i.*, g.title AS groupname, c.name AS category, v.name AS author, w.name as moderator, u.name AS editor FROM site_k2_items as i LEFT JOIN site_k2_categories AS c ON c.id = i.catid LEFT JOIN site_viewlevels AS g ON g.id = i.access LEFT JOIN site_users AS u ON u.id = i.checked_out LEFT JOIN site_users AS v ON v.id = i.created_by LEFT JOIN site_users AS w ON w.id = i.modified_by WHERE i.trash=0 ORDER BY i.id DESC LIMIT 0, 20;

---

4. Executed 2h 53m 2s ago for 10.598193 sec on Database --> newsbomb_site
Date: 2016-09-13 05:54:17 Query_time: 10.598193 Rows_examined: 68722: Rows_sent 53571 Lock_time: 0.020998
SELECT tag.name, tag.id FROM site_k2_tags as tag LEFT JOIN site_k2_tags_xref AS xref ON xref.tagID = tag.id WHERE xref.itemID IN (800,829,853,859,984,884,885,.......... LOTS OF IDS HERE ............,14203,47971) AND tag.published = 1;

---

5. Executed 2h 53m 2s ago for 10.588164 sec on Database --> newsbomb_site
Date: 2016-09-13 05:54:17 Query_time: 10.588164 Rows_examined: 68722: Rows_sent 53571 Lock_time: 0.018641
SELECT tag.name, tag.id FROM site_k2_tags as tag LEFT JOIN site_k2_tags_xref AS xref ON xref.tagID = tag.id WHERE xref.itemID IN (800,829,853,859,984,884,885,.......... LOTS OF IDS HERE ............,14203,47971) AND tag.published = 1;

---

6. Executed 2h 53m 2s ago for 10.561908 sec on Database --> newsbomb_site
Date: 2016-09-13 05:54:17 Query_time: 10.561908 Rows_examined: 68722: Rows_sent 53571 Lock_time: 0.016645
SELECT tag.name, tag.id FROM site_k2_tags as tag LEFT JOIN site_k2_tags_xref AS xref ON xref.tagID = tag.id WHERE xref.itemID IN (800,829,853,859,984,884,885,.......... LOTS OF IDS HERE ............,14203,47971) AND tag.published = 1;

---

Hello,

Yes the tag cloud can be intensive since the module has to go through potentially thousands upon thousands of tags. If the module is not cached then it will cause issues.

A custom menu towards tags would be preferable.
As for the backend, how was this query produced, where you using the filters?

Sorry for the delay,

About the TAG module everything is clear to me!

Currently the page is managed from our client and they don't remember
exactly which filters are using.
I am sending you the results that give Joomla debug gives when you follow this steps:
Login -> Components -> K2 -> Items

prntscr.com/cl3eox
prntscr.com/cl3glq
prntscr.com/cl3fuw

Its so embarrassing for us to know that the cause of sending our site down is the
backend of k2. Hope your can give us some useful tips to fix this.

Here is the K2 configuration parameters in the database
drive.google.com/file/d/0B_7CphkekgxgeHFWWDZhMGh3dHM/view?usp=sharing

If you want some extra information regarding the site please feel free to ask.

Hello,

You can try caching that specific query.
You can also move the administrator to a separate server so you can free up resources.

I will forward your suggestion to the hosting support.
Can we modify k2 back end to not be so stressful to the database?!

Thank you!

Hacking it will render it almost un-updatable.
I would track down the slow queries and cache them, they are most likely the queries that are used when the administrator filters are being used.

You can also select the "Disable ordering compacting" setting in K2's advanced settings if you haven't done so.

Hello again!
Finally i found the source of this issue, it was a multi category plugin for k2 causing this.
That's why over the web you could not find this weird query.

I am posting here the faulty plugin Inceptive Multiple Categories for K2
prntscr.com/d4a6ao

You need to let the plugin's developer know about this so they can provide a fix.