CiviCRM Community Forums (archive)

After upgrading from 3.2.3 to 3.4.7 on a test server, the Quick Search auto-complete no longer works. It spins, but no data is returned. If I look at processes in the database I see a a number of "SELECT DISTINCT(id), data FROM..." that have been churning for minutes.

If show the full process and run one of the SELECTs by hand from the mysql command line, the query is taking 34+ seconds. Interestingly if I switch over to my production database the query takes just as long, but the auto-complete works fine in the 3.2.3 machine.

Has the Quick Search query changed from 3.2.3 to 3.4.7?

Any pointers on what I should look at next to help resolve this would be greatly appreciated.

mysql> show processlist;
+-------+-------+-----------+---------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id    | User  | Host      | db            | Command | Time | State        | Info                                                                                                 |
+-------+-------+-----------+---------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 40230 | jason | localhost | dev1_civicrm  | Query   |    0 | NULL         | show processlist                                                                                     |
| 40433 | imba  | localhost | dev1_drupal   | Sleep   |  724 |              | NULL                                                                                                 |
| 40435 | imba  | localhost | dev1_civicrm  | Query   |  724 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40436 | imba  | localhost | dev1_drupal   | Sleep   |  724 |              | NULL                                                                                                 |
| 40438 | imba  | localhost | dev1_civicrm  | Query   |  724 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40439 | imba  | localhost | dev1_drupal   | Sleep   |  723 |              | NULL                                                                                                 |
| 40440 | imba  | localhost | dev1_civicrm  | Query   |  723 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40441 | imba  | localhost | dev1_drupal   | Sleep   |  723 |              | NULL                                                                                                 |
| 40442 | imba  | localhost | dev1_civicrm  | Query   |  723 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40443 | imba  | localhost | dev1_drupal   | Sleep   |  723 |              | NULL                                                                                                 |
| 40444 | imba  | localhost | dev1_civicrm  | Query   |  723 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40445 | imba  | localhost | dev1_drupal   | Sleep   |  723 |              | NULL                                                                                                 |
| 40446 | imba  | localhost | dev1_civicrm  | Query   |  723 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40448 | imba  | localhost | dev1_drupal   | Sleep   |  722 |              | NULL                                                                                                 |
| 40449 | imba  | localhost | dev1_civicrm  | Query   |  722 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40450 | imba  | localhost | dev1_drupal   | Sleep   |  722 |              | NULL                                                                                                 |
| 40451 | imba  | localhost | dev1_civicrm  | Query   |  722 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40452 | imba  | localhost | dev1_drupal   | Sleep   |  721 |              | NULL                                                                                                 |
| 40453 | imba  | localhost | dev1_civicrm  | Query   |  721 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40454 | imba  | localhost | dev1_drupal   | Sleep   |  721 |              | NULL                                                                                                 |
| 40455 | imba  | localhost | dev1_civicrm  | Query   |  721 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |
| 40456 | imba  | localhost | dev1_drupal   | Sleep   |  719 |              | NULL                                                                                                 |
| 40457 | imba  | localhost | dev1_civicrm  | Query   |  719 | Sending data | SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ' |

Good pointer, I'll check to see if the configs are the same, might account for the difference. Here is the full SELECT...

SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', sort_name, email, city, ste.name ) as data, sort_name
          FROM   civicrm_contact cc LEFT JOIN civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 )  LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1)   LEFT JOIN civicrm_state_province ste ON ( sts.state_province_id = ste.id  )
                 
                 
                  WHERE ( email LIKE 'bertolacci,' OR sort_name LIKE 'bertolacci,'  OR nick_name LIKE 'bertolacci,' )  AND cc.is_deleted = 0 
          LIMIT 0, 10 )
        UNION
        ( SELECT 1 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', sort_name, email, city, ste.name ) as data, sort_name
          FROM   civicrm_contact cc LEFT JOIN civicrm_email eml ON ( cc.id = eml.contact_id AND eml.is_primary = 1 )  LEFT JOIN civicrm_address sts ON ( cc.id = sts.contact_id AND sts.is_primary = 1)   LEFT JOIN civicrm_state_province ste ON ( sts.state_province_id = ste.id  )
                 
                 
                  WHERE ( email LIKE 'bertolacci,%' OR sort_name LIKE 'bertolacci,%'  OR nick_name LIKE 'bertolacci,%' )  AND cc.is_deleted = 0 
          ORDER BY sort_name
          LIMIT 0, 10 )
       ) t
ORDER BY exactFirst, sort_name

Looks like there have been some auto-complete changes, but I am not seeing the code changes tracked in FishEye so I can investigate further. Xavier this is your issue right?

http://issues.civicrm.org/jira/browse/CRM-6239

Ok, I think I have identified the source of the trouble. I found the sql in CRM/Contact/Page/AJAX.php. Looks like two separate issues have stepped on each other's toes a little. CRM-5954 identified unnecessary DISTINCTs and JOINs coupled with ORDER BY leading to the creation of temporary tables and of course taking more time. Here is the patched query after CRM-5954:

        $query = "
SELECT id, data
FROM (
   SELECT cc.id as id, CONCAT_WS( ' :: ', {$select} ) as data, sort_name
   FROM civicrm_contact cc {$from}
   {$aclFrom}
   {$additionalFrom}
   {$whereClause}
   LIMIT 0, {$limit}
     ) t
ORDER BY sort_name
";

CRM-7931 modify the autocomplete query to show any exact matches first and reintroduced the DISTINCT statement. The resulting temp tables that are created are breaking autocomplete in my install. Here is the query after CRM-7931:

         $query = "
SELECT DISTINCT(id), data
FROM   (
        ( SELECT 0 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$select} ) as data, sort_name
          FROM   civicrm_contact cc {$from}
                 {$aclFrom}
                 {$additionalFrom} {$includeEmailFrom}
                 {$exactWhereClause}
          LIMIT 0, {$limit} )
        UNION
        ( SELECT 1 as exactFirst, cc.id as id, CONCAT_WS( ' :: ', {$select} ) as data, sort_name
          FROM   civicrm_contact cc {$from}
                 {$aclFrom}
                 {$additionalFrom} {$includeEmailFrom}
                 {$whereClause}
          ORDER BY sort_name
          LIMIT 0, {$limit} )
       ) t
ORDER BY exactFirst, sort_name
LIMIT    0, {$limit}
";

I'm going to roll the query back to the prior version to see if all is well, but does something need to be done to reconcile these two issues?

Some direct links...
http://issues.civicrm.org/jira/browse/CRM-5954
http://issues.civicrm.org/jira/browse/CRM-7931

Rolling back the code did work for me.