MySQL uses filesort index when building the query which can cause performance issues
MySQL uses filesort index when building the query which can cause performance issues. This can be solved by simply removing the GROUP BY and ORDER BY options from query in function alphabetQuery, CRM/Contact/BAO/Query.php see https://github.com/civicrm/civicrm-core/blob/master/CRM/Contact/BAO/Query.php#L5012
Original query:
$query = "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
{$sqlParts['from']}
{$sqlParts['where']}
{$sqlParts['having']}
GROUP BY sort_name
ORDER BY sort_name asc";
Change query to:
$query = "SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name
{$sqlParts['from']}
{$sqlParts['where']}
{$sqlParts['having']}";
This change has no visible impact on the search results pager or listing.
The query was generated by CiviCRM when doing an Advanced Search and selecting 6 CiviCRM Groups (not Smart Groups) - displaying results as contacts.
Before this change, note the "Using filesort" on 24754 rows. Query takes longer than 90 seconds to complete - can trigger a PHP timeout.
MariaDB [ajpulmse_crm]> explain SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-5e742600a44d
7` ON (contact_a.id = `civicrm_group_contact-5e742600a44d7`.contact_id AND `civicrm_group_contact-5e742600a44d7`.status IN ('Added')) LEFT JOIN civicrm_group_contact `civicrm_group_contact-
5e742600a47a7` ON (contact_a.id = `civicrm_group_contact-5e742600a47a7`.contact_id AND `civicrm_group_contact-5e742600a47a7`.status IN ('Added')) LEFT JOIN civicrm_group_contact `civicrm_gr
oup_contact-5e742600a4a78` ON (contact_a.id = `civicrm_group_contact-5e742600a4a78`.contact_id AND `civicrm_group_contact-5e742600a4a78`.status IN ('Added')) LEFT JOIN civicrm_group_contact
`civicrm_group_contact-5e742600a4d39` ON (contact_a.id = `civicrm_group_contact-5e742600a4d39`.contact_id AND `civicrm_group_contact-5e742600a4d39`.status IN ('Added')) LEFT JOIN civicrm_g
roup_contact `civicrm_group_contact-5e742600a4fde` ON (contact_a.id = `civicrm_group_contact-5e742600a4fde`.contact_id AND `civicrm_group_contact-5e742600a4fde`.status IN ('Added')) LEFT JO
IN civicrm_group_contact `civicrm_group_contact-5e742600a529d` ON (contact_a.id = `civicrm_group_contact-5e742600a529d`.contact_id AND `civicrm_group_contact-5e742600a529d`.status IN ('Adde
d')) WHERE ( ( ( ( `civicrm_group_contact-5e742600a44d7`.group_id IN ("498") ) ) ) OR ( ( ( `civicrm_group_contact-5e742600a47a7`.group_id IN ("499") ) ) ) OR ( ( ( `civicrm_group_contact-5
e742600a4a78`.group_id IN ("505") ) ) ) OR ( ( ( `civicrm_group_contact-5e742600a4d39`.group_id IN ("504") ) ) ) OR ( ( ( `civicrm_group_contact-5e742600a4fde`.group_id IN ("503") ) ) ) OR
( ( ( `civicrm_group_contact-5e742600a529d`.group_id IN ("502") ) ) ) ) AND (contact_a.is_deleted = 0) GROUP BY sort_name;
+------+-------------+-------------------------------------+------+----------------------------+----------------------------+---------+---------------------------+-------+------------------
-----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------+-------------+-------------------------------------+------+----------------------------+----------------------------+---------+---------------------------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | contact_a | ref | index_is_deleted_sort_name | index_is_deleted_sort_name | 1 | const | 24754 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | civicrm_group_contact-5e742600a44d7 | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Distinct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a47a7 | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Distinct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a4a78 | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Distinct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a4d39 | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Distinct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a4fde | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Distinct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a529d | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Distinct |
+------+-------------+-------------------------------------+------+----------------------------+----------------------------+---------+---------------------------+-------+-----------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
After this change, note absence of the "Using filesort". Query completes in 5 seconds or less.
MariaDB [ajpulmse_crm]> explain
-> SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-5e742600a44d7` ON (contact_a.id = `civ
icrm_group_contact-5e742600a44d7`.contact_id AND `civicrm_group_contact-5e742600a44d7`.status IN ('Added')) LEFT JOIN civicrm_group_contact `civicrm_group_contact-5e742600a47a7` ON (contact
_a.id = `civicrm_group_contact-5e742600a47a7`.contact_id AND `civicrm_group_contact-5e742600a47a7`.status IN ('Added')) LEFT JOIN civicrm_group_contact `civicrm_group_contact-5e742600a4a78`
ON (contact_a.id = `civicrm_group_contact-5e742600a4a78`.contact_id AND `civicrm_group_contact-5e742600a4a78`.status IN ('Added')) LEFT JOIN civicrm_group_contact `civicrm_group_contact-5e
742600a4d39` ON (contact_a.id = `civicrm_group_contact-5e742600a4d39`.contact_id AND `civicrm_group_contact-5e742600a4d39`.status IN ('Added')) LEFT JOIN civicrm_group_contact `civicrm_grou
p_contact-5e742600a4fde` ON (contact_a.id = `civicrm_group_contact-5e742600a4fde`.contact_id AND `civicrm_group_contact-5e742600a4fde`.status IN ('Added')) LEFT JOIN civicrm_group_contact `
civicrm_group_contact-5e742600a529d` ON (contact_a.id = `civicrm_group_contact-5e742600a529d`.contact_id AND `civicrm_group_contact-5e742600a529d`.status IN ('Added')) WHERE ( ( ( ( `civicr
m_group_contact-5e742600a44d7`.group_id IN ("498") ) ) ) OR ( ( ( `civicrm_group_contact-5e742600a47a7`.group_id IN ("499") ) ) ) OR ( ( ( `civicrm_group_contact-5e742600a4a78`.group_id IN
("505") ) ) ) OR ( ( ( `civicrm_group_contact-5e742600a4d39`.group_id IN ("504") ) ) ) OR ( ( ( `civicrm_group_contact-5e742600a4fde`.group_id IN ("503") ) ) ) OR ( ( ( `civicrm_group_conta
ct-5e742600a529d`.group_id IN ("502") ) ) ) ) AND (contact_a.is_deleted = 0);
+------+-------------+-------------------------------------+------+----------------------------+----------------------------+---------+---------------------------+-------+------------------
------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------+-------------+-------------------------------------+------+----------------------------+----------------------------+---------+---------------------------+-------+------------------
------------+
| 1 | SIMPLE | contact_a | ref | index_is_deleted_sort_name | index_is_deleted_sort_name | 1 | const | 24754 | Using index; Usin
g temporary |
| 1 | SIMPLE | civicrm_group_contact-5e742600a44d7 | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Dist
inct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a47a7 | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Dist
inct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a4a78 | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Dist
inct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a4d39 | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Dist
inct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a4fde | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Dist
inct |
| 1 | SIMPLE | civicrm_group_contact-5e742600a529d | ref | UI_contact_group | UI_contact_group | 4 | ajpulmse_crm.contact_a.id | 1 | Using where; Dist
inct |
+------+-------------+-------------------------------------+------+----------------------------+----------------------------+---------+---------------------------+-------+------------------
------------+
7 rows in set (0.00 sec)
Agileware ref: CIVICRM-1457