CiviCRM Community Forums (archive)

We have 2 sites we recently upgraded to 3.4.1 and both have recently fallen over due to problematic queries. I am not sure if this is new as we see a similar (from memory) query in the slow log on a 3.2 install and both sites are on a new server as well which may have made the symptoms worse.

However, Torrance was able to identify the slow query & to cut it down to the part that causes the problem.

I haven't figured out the source of the query but I have figured out what it would look like if it were to be gentle on the server

This query is an excerpt of the slow query and cannot complete on our server - explain statement suggests it is examining 633 million rows - 38000 * 15,000. Query ran for over 2 hours on customer server before we killed it (assume T is doing something so no query can run that long ever again)

SELECT count( DISTINCT ( civicrm_activity.id ) )  FROM civicrm_contact contact_a
LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id
LEFT JOIN civicrm_activity
ON ( civicrm_activity.id = civicrm_activity_target.activity_id OR civicrm_activity.source_contact_id = contact_a.id )



This is the same query using a union (union gets rid of the duplicates according to the manual so I didn't use distinct but counted them after the duplicates - this one finishes happily in a second or so

SELECT count( a.id )
FROM (
(

SELECT civicrm_activity.id
FROM civicrm_contact contact_a
LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id
LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id )
WHERE civicrm_activity.id IS NOT NULL
)
UNION (

SELECT civicrm_activity.id
FROM civicrm_contact contact_a
LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id
LEFT JOIN civicrm_activity ON ( civicrm_activity.source_contact_id = contact_a.id )
WHERE civicrm_activity.id IS NOT NULL
)
) AS a

I don't know what called the query here but the query is defined if the from function of activity bao

line 387 est

                $from .= " $side JOIN civicrm_activity
                                      ON ( ( ( civicrm_activity.id = civicrm_activity_assignment.activity_id )
                                               OR ( civicrm_activity.id = civicrm_activity_target.activity_id ) )


In this join the part after the OR will never use an index.

I think the bit about the distinct is a red herring

SELECT civicrm_activity.id  FROM civicrm_contact contact_a
LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id
LEFT JOIN civicrm_activity
ON ( civicrm_activity.id = civicrm_activity_target.activity_id OR civicrm_activity.source_contact_id = contact_a.id )

is enough to crash our server if I run it against about 38000 contact with a av 3 activities

This is the main BAO function

So that's when doing a search on activities or the activity tab ?

X+

OK, just checked & doing a blank search from find activities causes the server to crash on this query

Hold on, you don't have timeout on your php ? is the mysql query still running the the calling script timesout ?

X+

I still think that a query that examines 6 million rows just cos a user clicked on a search is pretty problematic

Wouldn't argue on that one.  Wondering if there is a "proper" way of identifying these queries before it hits us on the prod. Eg if we run the test suite with a big table and see if they are slow queries in the log (but would probably need weeks to run...)

X+

But, you can set a max execution time (or similar) in SQL can't you?