Keyword

Slow query issue

  • Klaus Veliu
  • Klaus Veliu's Avatar Topic Author
  • Offline
  • Senior Member
More
7 years 11 months ago #153325 by Klaus Veliu
Slow query issue was created by Klaus Veliu
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

Please Log in or Create an account to join the conversation.

  • JoomlaWorks Support Team
  • JoomlaWorks Support Team's Avatar
  • Offline
  • Elite Member
More
7 years 11 months ago #153349 by JoomlaWorks Support Team
Replied by JoomlaWorks Support Team on topic Slow query issue
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.

JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)

Please Log in or Create an account to join the conversation.

  • Klaus Veliu
  • Klaus Veliu's Avatar Topic Author
  • Offline
  • Senior Member
More
7 years 11 months ago #153360 by Klaus Veliu
Replied by Klaus Veliu on topic Slow query issue
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.

Please Log in or Create an account to join the conversation.

  • JoomlaWorks Support Team
  • JoomlaWorks Support Team's Avatar
  • Offline
  • Elite Member
More
7 years 11 months ago #153369 by JoomlaWorks Support Team
Replied by JoomlaWorks Support Team on topic Slow query issue
Do not search for the whole query. Make a search for SELECT DISTINCT i.*

JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)

Please Log in or Create an account to join the conversation.

  • Klaus Veliu
  • Klaus Veliu's Avatar Topic Author
  • Offline
  • Senior Member
More
7 years 11 months ago #153371 by Klaus Veliu
Replied by Klaus Veliu on topic Slow query issue

Please Log in or Create an account to join the conversation.

  • Klaus Veliu
  • Klaus Veliu's Avatar Topic Author
  • Offline
  • Senior Member
More
7 years 11 months ago - 7 years 11 months ago #153423 by Klaus Veliu
Replied by Klaus Veliu on topic Slow query issue
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!
Last edit: 7 years 11 months ago by Klaus Veliu. Reason: Adding description how to open the report.

Please Log in or Create an account to join the conversation.

  • JoomlaWorks Support Team
  • JoomlaWorks Support Team's Avatar
  • Offline
  • Elite Member
More
7 years 11 months ago #153508 by JoomlaWorks Support Team
Replied by JoomlaWorks Support Team on topic Slow query issue
- 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.

JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)

Please Log in or Create an account to join the conversation.

  • Klaus Veliu
  • Klaus Veliu's Avatar Topic Author
  • Offline
  • Senior Member
More
7 years 11 months ago #153530 by Klaus Veliu
Replied by Klaus Veliu on topic Slow query issue
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

Please Log in or Create an account to join the conversation.

  • Klaus Veliu
  • Klaus Veliu's Avatar Topic Author
  • Offline
  • Senior Member
More
7 years 6 months ago #157790 by Klaus Veliu
Replied by Klaus Veliu on topic Slow query issue
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;

Please Log in or Create an account to join the conversation.

  • Krikor Boghossian
  • Krikor Boghossian's Avatar
  • Offline
  • Platinum Member
More
7 years 6 months ago #157793 by Krikor Boghossian
Replied by Krikor Boghossian on topic Slow query issue
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?

JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)

Please Log in or Create an account to join the conversation.


Powered by Kunena Forum