Solved

Diagnose slow mysql query

Posted on 2011-03-18
12
327 Views
Last Modified: 2012-05-11
Hi experts,

I have a query that is generated by a view in my Drupal website, so I don't have much control over the actual SQL. Problem I am having is that the query takes around 120 seconds to execute. If I reorder the query and run it I can do it in less than a second.

The original query is below

 
SELECT DISTINCT node.nid 
                AS 
                nid, 
                node.title 
                AS node_title, 
node_data_field_event_session_date_time.field_event_session_date_time_value AS 
node_data_field_event_session_date_time_field_event_session_date_time_value, 
location.latitude                                                           AS 
                gmap_lat, 
location.longitude                                                          AS 
                gmap_lon 
FROM   node node 
       LEFT JOIN content_field_event_session_date_time 
                 node_data_field_event_session_date_time 
         ON node.vid = node_data_field_event_session_date_time.vid 
       LEFT JOIN content_field_event_session_date_time 
                 node_data_field_event_session_date_time2 
         ON node.vid = node_data_field_event_session_date_time2.vid 
       LEFT JOIN content_field_event_session_date_time 
                 node_data_field_event_session_date_time3 
         ON node.vid = node_data_field_event_session_date_time3.vid 
       LEFT JOIN location_instance location_instance 
         ON node.vid = location_instance.vid 
       LEFT JOIN location location 
         ON location_instance.lid = location.lid 
       INNER JOIN domain_access da_admin 
         ON node.nid = da_admin.nid 
WHERE  ( ( da_admin.gid = 0 
           AND da_admin.realm = 'domain_site' ) 
          OR ( da_admin.gid = 6 
               AND da_admin.realm = 'domain_id' ) ) 
       AND ( ( ( node.status <> 0 ) 
               AND ( node.TYPE IN ( 'event' ) ) ) 
             AND 
       ( Date_format(Addtime( 
node_data_field_event_session_date_time.field_event_session_date_time_value2, 
Sec_to_time( 
node_data_field_event_session_date_time.field_event_session_date_time_offset)), 
'%Y-%m-%d') >= '2011-03-18' ) ) 
ORDER  BY 
node_data_field_event_session_date_time_field_event_session_date_time_value ASC

Open in new window


If I move the location and location_instance joins to the top of the from clause all my problems go away. I am wondering if anyone knows the reason for this and how to fix it without reordering the query? Let me know if you need to see the schema.

Regards,
Michael
0
Comment
Question by:mstrelan
12 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 350 total points
ID: 35169938
Hi mstrelan,

I suspect that there are a lot fewer rows in domain_access than in node.

Your query starts with a series of outer joins.  That means that the results of the join will have AT LEAST as many rows as the base table (node).  Remember that the result of a join is a derived table and that a derived table, by definition is not indexed.  So after the first outer join you write a derived table, then do a full table scan on it to join the next table.  If there are a lot of rows, this is time consuming.

An INNER join is a filter.  It reduces the number of rows in the resulting derived table by filtering out rows as part of the join operation.  By doing the inner join first, you generate fewer rows in the derived table this is used for the series of outer joins.


So in a nutshell, by doing the inner join first, you filter out rows before you do outer joins to them.  By doing the inner join last you outer join data that you'll never use and filter the results last.

I don't know of a way to force the order of the joins in MySQL and still use the indexes other than restructuring the SQL.


Good Luck,
Kent
0
 
LVL 10

Author Comment

by:mstrelan
ID: 35169958
Awesome, thanks for the explanation. Would index hints help? I saw this page http://dev.mysql.com/doc/refman/5.1/en/index-hints.html but wasn't sure how to use it.
0
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 150 total points
ID: 35170430
I see you join with table content_field_event_session_date_time 3 times
But there is no uses of 2nd and 3rd called in the query
remove it might help on the speed

SELECT DISTINCT 
	node.nid AS nid, 
	node.title AS node_title, 
	node_data_field_event_session_date_time.field_event_session_date_time_value AS node_data_field_event_session_date_time_field_event_session_date_time_value,
	location.latitude AS gmap_lat, 
	location.longitude AS gmap_lon 
FROM   node node 
       INNER JOIN domain_access da_admin ON node.nid = da_admin.nid 
       LEFT JOIN content_field_event_session_date_time node_data_field_event_session_date_time 
         ON node.vid = node_data_field_event_session_date_time.vid 
       LEFT JOIN location_instance location_instance ON node.vid = location_instance.vid 
       LEFT JOIN location location ON location_instance.lid = location.lid 
WHERE
	(
	  ( da_admin.gid = 0 AND da_admin.realm = 'domain_site' ) 
	    OR
	  ( da_admin.gid = 6 AND da_admin.realm = 'domain_id' )
	)
	AND node.status <> 0
	AND node.TYPE IN ( 'event' )
	AND 
	Date_format(
	  Addtime(node_data_field_event_session_date_time.field_event_session_date_time_value2, 
	    Sec_to_time(node_data_field_event_session_date_time.field_event_session_date_time_offset)), 
	'%Y-%m-%d') >= '2011-03-18' 
ORDER  BY 
	node_data_field_event_session_date_time_field_event_session_date_time_value ASC

Open in new window

0
 
LVL 10

Author Comment

by:mstrelan
ID: 35170437
Joe... in the interface there is an option to filter between 2 sets of dates. This query is the default with those dates not set.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35170443
I see,  first of all, try to see if my given code can do any better, in term of result speed.

and, please provide query when 2 sets of date is apply to filter.
I would like to see if I can do something with it.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35171449
do you potentially get multiple rows from location?

or would 1 suffice?

could you create a view as

select .....
        ,(select lat from ....) as gmap_lat
        ,(select long from ...) as gmap_long
from ....

and use that without the left joins to location....
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 10

Author Comment

by:mstrelan
ID: 35177200
@JoeNuvo

Yes, reducing to only one join for the dates speeds up the query. But filtering the date result set with a date range doesn't help.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35177277
still waiting for your version of query which all dates is set (which required 2nd and 3rd table to perform join)
0
 
LVL 10

Author Comment

by:mstrelan
ID: 35177335
@JoeNuvo
Here is with all the date filters

SELECT DISTINCT node.nid
                AS
                nid,
                node.title
                AS node_title,
node_data_field_event_session_date_time.field_event_session_date_time_value AS
node_data_field_event_session_date_time_field_event_session_date_time_value,
location.latitude                                                           AS
                gmap_lat,
location.longitude                                                          AS
                gmap_lon
FROM   node node
       LEFT JOIN location_instance location_instance
         ON node.vid = location_instance.vid
       LEFT JOIN location location
         ON location_instance.lid = location.lid
       LEFT JOIN content_field_event_session_date_time
                 node_data_field_event_session_date_time
         ON node.vid = node_data_field_event_session_date_time.vid
       LEFT JOIN content_field_event_session_date_time
                 node_data_field_event_session_date_time2
         ON node.vid = node_data_field_event_session_date_time2.vid
       LEFT JOIN content_field_event_session_date_time
                 node_data_field_event_session_date_time3
         ON node.vid = node_data_field_event_session_date_time3.vid
       INNER JOIN domain_access da_admin
         ON node.nid = da_admin.nid
WHERE  ( ( da_admin.gid = 0
           AND da_admin.realm = 'domain_site' )
          OR ( da_admin.gid = 6
               AND da_admin.realm = 'domain_id' ) )
       AND ( ( ( node.status <> 0 )
               AND ( node.TYPE IN ( 'event' ) ) )
             AND
       ( ( Date_format(Addtime(
node_data_field_event_session_date_time.field_event_session_date_time_value,
Sec_to_time(
node_data_field_event_session_date_time.field_event_session_date_time_offset)),
    '%Y-%m-%d') >= '2011-05-01'
AND Date_format(Addtime(
node_data_field_event_session_date_time.field_event_session_date_time_value,
Sec_to_time(
node_data_field_event_session_date_time.field_event_session_date_time_offset)),
'%Y-%m-%d') <= '2011-05-31' )
AND ( Date_format(Addtime(
node_data_field_event_session_date_time.field_event_session_date_time_value2,
Sec_to_time(
node_data_field_event_session_date_time.field_event_session_date_time_offset)),
'%Y-%m-%d') >= '2011-05-01'
AND
Date_format(
Addtime(
node_data_field_event_session_date_time.field_event_session_date_time_value2,
  Sec_to_time(
node_data_field_event_session_date_time.field_event_session_date_time_offset)),
'%Y-%m-%d') <= '2011-05-31' )
AND ( Date_format(Addtime(
node_data_field_event_session_date_time.field_event_session_date_time_value2,
Sec_to_time(
node_data_field_event_session_date_time.field_event_session_date_time_offset)),
'%Y-%m-%d') >= '2011-03-21' ) ) )
ORDER  BY
node_data_field_event_session_date_time_field_event_session_date_time_value ASC
LIMIT  0, 20 

Open in new window

0
 
LVL 10

Assisted Solution

by:mstrelan
mstrelan earned 0 total points
ID: 35177376
@Everyone

I have solved the problem. After considering the comment from Kdo I added a where clause "location.latitude != 0" and was able to move this to the top via the Drupal view builder. This moved the location join to the top and has sped up my query considerably.

@JoeNuvo - feel free to keep working on improving this but for now I am happy with this solution
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35177662
Your query with all dates filter, I still don't see any uses of 2nd and 3rd join of table content_field_event_session_date_time
so, I still recommend you to remove it from your query.

to combine with Kdo's comment, you shall move INNER JOIN of table domain_access to be 1st table to join. (as in my given query)
0
 
LVL 10

Author Closing Comment

by:mstrelan
ID: 35213342
Included my comment in the solution as it explains how to fix it in Drupal
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now