asked on

Diagnose slow mysql query

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

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mstrelan

ASKER

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.

SOLUTION

JoeNuvo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mstrelan

ASKER

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.

JoeNuvo

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.

Lowfatspread

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....

mstrelan

ASKER

@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.

JoeNuvo

still waiting for your version of query which all dates is set (which required 2nd and 3rd table to perform join)

mstrelan

ASKER

@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

SOLUTION

mstrelan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JoeNuvo

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)

mstrelan

ASKER

Included my comment in the solution as it explains how to fix it in Drupal