Please Help Optimize Slow Query

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
darkdim's picture

Hello!
Help to understand what to do. I optimizes views

The first boot - everything is bad ((
Executed 350 queries in 823.4 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 1367.98 ms.

SELECT DISTINCT node.nid AS nid, node.created AS node_created, node.title AS node_title, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node_data_field_urgently.field_urgently_value AS node_data_field_urgently_field_urgently_value, node.type AS node_type, node.vid AS node_vid, node.sticky AS node_sticky
FROM node node
INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid
LEFT JOIN content_type_photo_post node_data_field_urgently ON node.vid = node_data_field_urgently.vid
WHERE (
node.status =1
)
AND (
node.type
IN (
'video', 'text', 'photo'
)
)
ORDER BY node_sticky DESC , node_created DESC
LIMIT 0 , 6

According to phpmyadmin: Displays the line 0 - 5 ( 6 total , Query took 0.7448 sec.)

upgrade again (this request is not):
Executed 330 queries in 30.42 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 494.06 ms.

Why is that?

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE node ref PRIMARY,node_status_type,node_type node_status_type 4 const 23054 Using where; Using temporary; Using filesort
1 SIMPLE node_comment_statistics eq_ref PRIMARY PRIMARY 4 *.node.nid 1
1 SIMPLE node_data_field_urgently eq_ref PRIMARY PRIMARY 4 *.node.vid 1

Comments

Indexes and if possible time filter

nestor.mata's picture

Check if this columns has an index and if they don't create it:
node.nid
node_comment_statistics.nid
node_data_field_urgently.vid
node.type
node.sticky

After that, I notice that you are limiting to only 6 items. If you can warranty that the output is indeed not going to be older than X amount of time, lets say that you know for sure that the items posted there they will not be older than 1 month (only if you are sure this will always be true) then you could add something like this:
an OR filter that includes either the sticky flag or that the created time is greater than 1 month ago
The reason is that time filters are actually numerical filters and filtering by a numerical index is pretty fast if that can restrict the sample of data from where you are working with, using a time filter restricted to a month or something like that can eliminate years or months of data from the equation, leaving you with a really more smaller amount of data to analyze with the other filters or joins.

And never do joins on not indexed columns, if you need to, then create the index.

~Nestor

Thank you, I think that would

darkdim's picture

Thank you, I think that would add a filter by time
... and check indexes

Drupal is low on its first uncached access

ghankstef's picture

A Drupal site with no caching is slow in most cases. What I think you are seeing is that caching is kicking after the first request. There are 20 fewer sql queries and overall they execute must more quickly

Uncached page execution time of 1367.98 ms really isn't bad at all. I've seen implementations where page execution time is much higher than that.

On the query run EXPLAIN on it here is some detail on how to analyze slow queries.

http://blogs.mpr.org/developer/2013/06/using-mysqls-slow-query-log-to-pi...

Thanks, I'll check indexes

darkdim's picture

Thanks, I'll check indexes

I asked to tech support

darkdim's picture

I asked to tech support fine-tune mysql, speed is also increased, the time has decreased by almost 5 times