Posted by foxtrotcharlie on April 18, 2014 at 1:43pm
I have a views query that is my number 1 slow query. I have been trying to understand why it's slow and what to do about it, but I am struggling. So far I think that it's slow because it's using temporary table and a filesort, as shown in the 'Explain'. When I remove the 2nd "ORDER BY" statement (node_data_field_opportunity_closing_date_field_opportunity_closing_date_value ASC), no temporary table is used the query time is significantly reduced.
Is there a way to keep results ordered without generating a temporary table? Any other tips on this query would be appreciated :-)
This is from the slow query log analysis:
# Query 1: 0.01 QPS, 0.04x concurrency, ID 0xA9693D0E17DCD098 at byte 16628216
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.27 [1.0], V/M = 8.43
# Query_time sparkline: | ^_|
# Time range: 2014-04-12 06:32:10 to 2014-04-18 12:05:47
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 11 3589
# Exec time 9 20400s 2s 95s 6s 14s 7s 4s
# Lock time 0 79s 86us 11s 22ms 31ms 251ms 445us
# Rows sent 0 14.02k 4 4 4 4 0 4
# Rows examine 3 70.83M 20.04k 24.68k 20.21k 19.40k 694.08 19.40k
# Query size 6 4.97M 1.42k 1.42k 1.42k 1.42k 0 1.42k
#...<snipped>
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ ########And this is from the EXPLAIN of the original query (with the 2nd order by):
mysql> EXPLAIN /!50100 PARTITIONS/
-> SELECT node.nid AS nid,
-> node_revisions.teaser AS node_revisions_teaser,
-> node_revisions.format AS node_revisions_format,
-> node.vid AS node_vid,
-> node.title AS node_title,
-> node_data_field_opportunity_closing_date.field_opportunity_closing_date_value AS node_data_field_opportunity_closing_date_field_opportunity_closing_date_value,
-> node.type AS node_type,
-> node.sticky AS node_sticky
-> FROM node node
-> INNER JOIN content_type_opportunity node_data_field_opportunity_closing_date ON node.vid = node_data_field_opportunity_closing_date.vid
-> INNER JOIN content_type_opportunity node_data_field_opportunity_type ON node.vid = node_data_field_opportunity_type.vid
-> LEFT JOIN content_field_is_sn_announce node_data_field_is_sn_announce ON node.vid = node_data_field_is_sn_announce.vid AND node_data_field_is_sn_announce.field_is_sn_announce_value = '1'
-> LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
-> WHERE ((node.status = 1) AND (node.type in ('opportunity')) AND (node_data_field_opportunity_type.field_opportunity_type_value = 'Employment') AND (node_data_field_is_sn_announce.field_is_sn_announce_value IS NULL))
-> AND (DATE_FORMAT(STR_TO_DATE(node_data_field_opportunity_closing_date.field_opportunity_closing_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d\T%H:%i:%s') >= '2014-04-15T15:54:29')
-> ORDER BY node_sticky DESC, node_data_field_opportunity_closing_date_field_opportunity_closing_date_value ASC
-> LIMIT 0, 4;
+----+-------------+------------------------------------------+------------+--------+--------------------------------+------------------+---------+------------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------------------------+------------+--------+--------------------------------+------------------+---------+------------------------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | node | NULL | ref | vid,node_type,node_status_type | node_status_type | 103 | const,const | 4092 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | node_data_field_is_sn_announce | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ngopulse_mamp.node.vid | 1 | Using where |
| 1 | SIMPLE | node_data_field_opportunity_closing_date | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ngopulse_mamp.node.vid | 1 | Using where |
| 1 | SIMPLE | node_data_field_opportunity_type | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ngopulse_mamp.node_data_field_opportunity_closing_date.vid | 1 | Using where |
| 1 | SIMPLE | node_revisions | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ngopulse_mamp.node.vid | 1 | |
+----+-------------+------------------------------------------+------------+--------+--------------------------------+------------------+---------+------------------------------------------------------------+------+----------------------------------------------+
5 rows in set (0.07 sec)
Comments
Add an index
On the node_data_field_opportunity_closing_date table add an index to field_opportunity_closing_date_value. Fields do not index the contents of them (in this case "value"). If you use field values in a View/EFQ then that can be slow.
On a related note, if things workout I'll start development of a D7 version https://drupal.org/project/dbtuner in the next couple of months. I'm also noticing that a bunch of views show up in my slow query log and I would rather have a tool to fix them rather than go to each view. I would also like to have integration with EFQ as we use that in our code base & I've already fixed a couple of slow queries that were generated by it.
Already has that index
Thanks mikeytown2 :-) It already has an index, I think it was even created by your dbtuner module (this is a D6 site).
Try removing the teaser (or
Try removing the teaser (or the whole node_revisions table) from the query just for testing.
If it gets faster, you can use my views_optimized sandbox module to split the query into
A temporary table is always needed when a "blob" is retrieved and needs to be sorted.
In Drupal 7 the same is done explicitly by loading the "node IDs first" and then loading the data - potentically cached.
Good luck!
node_revisions removal
Thanks Fabianx :-) Removing node_revisions did reduce the time. I will definitely look at your module, thanks :-)