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

Performance: combine is_primary indexes into existing indexes.

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Won't Fix
    • Affects Version/s: 3.1.1
    • Fix Version/s: Unscheduled
    • Component/s: Core CiviCRM
    • Labels:
      None

      Description

      CiviCRM generally has too many indexes (which slows down inserts) and the existing indexes are not very well defined (which slows down searches). This stems from research that I've been doing in CRM-5556. One area for improvement is with indexes on any is_primary fields. We never do searches just for all primary records in a table. That search is always combined with something else, usually "show me the primary (email/address/etc.) for this contact". In fact that's the way we normally JOIN tables "INNER JOIN civicrm_foo f ON (c.id = f.contact_id AND f.is_primary = 1)". In these cases it's going to be better to combine these two indexes.

      This applies for civicrm_email, civicrm_address, and probably others.

        Attachments

        1. benchmark_combined
          6 kB
          dave hansen-lange
        2. benchmark_email_combined_index
          6 kB
          dave hansen-lange
        3. benchmark_email.php
          3 kB
          dave hansen-lange

          Activity

            People

            • Assignee:
              lobo Donald A. Lobo
              Reporter:
              dalin dave hansen-lange
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: