CiviCRM Community Forums (archive)

Hi,

we have a userbase of around 12000 people in our CiviCRM-System and I the one guy who tries to find out why sometimes the performance of our system is quite bad - some selects just take more than just some minutes.

One of the queries I found disturbing is the following one:


SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name 
FROM civicrm_contact contact_a 
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-101`
    ON contact_a.id = `civicrm_group_contact-101`.contact_id   
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-102`
    ON contact_a.id = `civicrm_group_contact-102`.contact_id   
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-46`
    ON contact_a.id = `civicrm_group_contact-46`.contact_id   
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-60`
    ON contact_a.id = `civicrm_group_contact-60`.contact_id   
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-6`
    ON contact_a.id = `civicrm_group_contact-6`.contact_id   
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-94`
   ON contact_a.id = `civicrm_group_contact-94`.contact_id   
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-95`
    ON contact_a.id = `civicrm_group_contact-95`.contact_id   
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-97`
    ON contact_a.id = `civicrm_group_contact-97`.contact_id   
  LEFT JOIN civicrm_group_contact `civicrm_group_contact-99`
    ON contact_a.id = `civicrm_group_contact-99`.contact_id   
  LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_6`
    ON contact_a.id = `civicrm_group_contact_cache_6`.contact_id   
WHERE 
  (
   (
    ( `civicrm_group_contact-6`.group_id IN ( 6 )
     AND
      `civicrm_group_contact-6`.status IN ("Added" )
     )
     OR
     ( `civicrm_group_contact_cache_6`.group_id = 6
     )
    )
   ) 
   AND 
   ( 
    ( `civicrm_group_contact-6`.group_id IN ( 6 )
    AND `civicrm_group_contact-6`.status IN ("Added")
   ) 
   OR 
   ( `civicrm_group_contact-46`.group_id IN ( 46 )
   AND `civicrm_group_contact-46`.status IN ("Added")
   ) 
  OR 
   ( `civicrm_group_contact-60`.group_id IN ( 60 )
   AND `civicrm_group_contact-60`.status IN ("Added")
  ) OR ( `civicrm_group_contact-94`.group_id IN ( 94 )
  AND `civicrm_group_contact-94`.status IN ("Added") ) 
  OR  ( `civicrm_group_contact-95`.group_id IN ( 95 )
  AND `civicrm_group_contact-95`.status IN ("Added") ) 
  OR  ( `civicrm_group_contact-97`.group_id IN ( 97 )
  AND `civicrm_group_contact-97`.status IN ("Added") ) 
  OR  ( `civicrm_group_contact-99`.group_id IN ( 99 )
  AND `civicrm_group_contact-99`.status IN ("Added") )   
  OR  ( `civicrm_group_contact-101`.group_id IN ( 101 )
  AND `civicrm_group_contact-101`.status IN ("Added") ) 
  OR  ( `civicrm_group_contact-102`.group_id IN ( 102 )
  AND `civicrm_group_contact-102`.status IN ("Added") )  );


I am not an expert on CiviCRM, I just have a test-account to check whether I can find some bugs, I just found this query by checking the processlist of mysql. I don't know how to "get" this via CiviCRM-Frontend-Access.
But: What is the reason for this statement? there are quite a lot of joins, but it doesn't seem to be needed - since all you are asking for is the following:


SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name 
FROM civicrm_contact contact_a;


or do I miss something there?
And if I am right, where could I "cut" the statement to that small part? Since that goes very fast with MySQL then... :-)

This is CiviCRM 2.1.4.