CiviCRM

Performance: combine is_primary indexes into existing indexes.

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 3.1.1
  • Fix Version/s: Future Version
  • 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. File
    benchmark_combined
    16/Mar/10 2:31 PM
    6 kB
    dave hansen-lange
  2. File
    benchmark_email_combined_index
    16/Mar/10 2:31 PM
    6 kB
    dave hansen-lange
  3. File
    benchmark_email.php
    16/Mar/10 2:31 PM
    3 kB
    dave hansen-lange

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Donald A. Lobo added a comment -

might want to combine all issues under one big issue and/or sub-tasks if you are going towork on it and submit patches to it
Show
Donald A. Lobo added a comment - might want to combine all issues under one big issue and/or sub-tasks if you are going towork on it and submit patches to it
Hide
Permalink
dave hansen-lange added a comment -
Here's some benchmarks to prove that I'm not totally off my rocker. This is done in a DB with ~610k contacts and ~620k emails. The comparison is between civicrm_email (which has my proposed modifications) and civicrm_email_original which is an exact copy, just with CiviCRM's default indexes.

The combined benefit of both the changes here and in [CRM-5794] are:
inserts: 2.11% improvement
searches: 1.28% - 2.67% improvement
deletes: 2.43% improvement
Show
dave hansen-lange added a comment - Here's some benchmarks to prove that I'm not totally off my rocker. This is done in a DB with ~610k contacts and ~620k emails. The comparison is between civicrm_email (which has my proposed modifications) and civicrm_email_original which is an exact copy, just with CiviCRM's default indexes. The combined benefit of both the changes here and in [CRM-5794] are: inserts: 2.11% improvement searches: 1.28% - 2.67% improvement deletes: 2.43% improvement

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
Atlassian JIRA (v4.4.4#664-r167664) | Report a problem
Powered by a free Atlassian JIRA open source license for CiviCRM . Try JIRA - bug tracking software for your team.