Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-5179

Full-text search queries cause system to crawl with larger datasets

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: 3.0.2
    • Fix Version/s: 3.0.1
    • Component/s: Core CiviCRM
    • Labels:
      None

      Description

      We only have about 4000 activities, but the system just dies with the additions made to full-text search. Looking at these queries shows they are written "old style" with comma inner joins which can be really inefficient.

      For example this one:

      $contactSQL[] = "
      SELECT ca.id
      FROM civicrm_activity ca, civicrm_activity_target cat, civicrm_contact c, civicrm_email e
      LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
      LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id )
      WHERE cat.activity_id = ca.id
      AND cat.target_contact_id = c.id
      AND ( c.display_name LIKE {$this->_text} OR
      ( e.email LIKE {$this->_text} AND cat.target_contact_id = e.contact_id AND
      ca.activity_type_id = ov.value AND ov.name IN ('Inbound Email', 'Email') ) )
      ";

      should be rewritten more like:

      SELECT ca.id
      FROM civicrm_activity ca INNER JOIN civicrm_activity_target cat ON cat.activity_id = ca.id
      INNER JOIN civicrm_contact c ON cat.target_contact_id = c.id
      INNER JOIN civicrm_email e ON cat.target_contact_id = e.contact_id
      LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
      LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id AND ca.activity_type_id = ov.value )
      WHERE ( c.display_name LIKE {$this->_text} OR
      ( e.email LIKE {$this->_text} AND ov.name IN ('Inbound Email', 'Email') ) )

        Attachments

          Activity

            People

            • Assignee:
              rahulbile Rahul Bile
              Reporter:
              demeritcowboy Dave D
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: