E-mail
I'm still gathering data. The slow query logs are actually informative, and I suspect you will recognize what part of the app generated the SQL. The enclosed file contain 7 slow queries we've seen more than 1 time, most of which take longer than a minute to execute. 5 of them take more than two. Since I'm working off only the latest slow log – since 080120 6:31:52 US EST – these deserve to be called frequent enough to look at.
One query in particular of importance I know of, that we have disabled on the site. The call to CRM_Contact_BAO_Contact::permissionedContact( $userID ) in civicrm_view_data() typically takes 30-60 seconds to execute. Peter Fischbach ran across this one, trying to view his own record: user/46018. This path still gets called; I believe someone encountered this issue 7 times during the life of this slow log.
A Simple Indexing Fix To Improve Joins On civicrm_custom_values
I tried the following in various scripts. It dramatically improves the speed of a query that left joins on civicrm_custom_value:
- First, do the following simple reindex on the table:
- Second, when doing a query on the table, make sure you name all three of these sub-keys, including entity_table. This is necessary so that the MySQL query planner clues in on the fact that (1) it should use an index, rather than do a full table scan, and (2) that's the right index to use. MySQL does not like to use multiple indexes.
The queries I'm testing are not just faster: they are orders of magnitude faster.
Another Killer Query
Here's a query that we are getting several times a day. It causes MySQL to take over the processor. Note the large number of LEFT JOIN}}s. I'm guessing that this table needs an index similar to the one I added to {{civicrm_custom_value.
I've done an EXPLAIN on this. Looks like the wrong index is getting chosen (the FK over the one we want). I've attached the output of EXPLAIN.
