Good afternoon

Recently I've installed Family tree module to my Drupal site. And after I've imported the existing data to the database, I realised that the Individuals list page is being generated for 3 to 7 seconds on my Core2Duo laptop (I have a database of 89000 records).

So I had to investigate what was the problem. The slow query was the following:

SELECT node.nid AS nid,
       family_individual.lastname AS family_individual_lastname,
       family_individual.firstname AS family_individual_firstname,
       family_individual.middlename AS family_individual_middlename,
       family_individual.gender AS family_individual_gender,
       family_individual.birthdate AS family_individual_birthdate,
       family_individual.deathdate AS family_individual_deathdate
FROM dru_node node
   LEFT JOIN dru_family_individual family_individual
   ON node.nid = family_individual.nid
WHERE (node.type in ('family_individual')) AND (node.status <> 0)
ORDER BY family_individual_lastname DESC LIMIT 0, 40

It's explain command is:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE node index node_status_type,node_type node_status_type 106 NULL 88930 100.00 Using where; Using index; Using temporary; Using filesort
1 SIMPLE family_individual eq_ref PRIMARY,nid_vid PRIMARY 4 rodstvo.node.nid 1 100.00

The solution for the query I've found is: using INNER JOIN, instead of LEFT JOIN. In this case it took only 0.003 sec to execute the query for the DB. (I should mention also, that I've added a `dru_family_individual`.`lastname` index, that is not used in case of LEFT JOIN).

And not knowing much about coding for Drupal, I'm not sure how to implement this change to the code. And be greatful for help. And I hope this change could be usefull to someone else.

Scencerely yours,
Soul88

Comments

soul88’s picture

soul88
Primary language Ukrainian
Location Ukraine
commented

Oh, just forgot to mention, that I have 5.1.40-community MySQL server.

soul88’s picture

soul88
Primary language Ukrainian
Location Ukraine
commented
Status: Active » Needs review

I've just added this function to the end of family.module file

function family_views_data_alter(&$data) {
  // By using INNER joins on all CCK fields and workflow states, can
  // dramatically improve performance.
  // See <a href="http://drupal.org/node/372994" title="http://drupal.org/node/372994" rel="nofollow">http://drupal.org/node/372994</a> for more details.
//print_r($data);  die();
  foreach ($data as $element => $info) {
    if (strpos($element, 'node_data_field_') === 0
        || $element == 'family_individual'
        || $element == 'family_group'
        || $element == 'family_location')
    {
      $data[$element]['table']['join']['node']['type'] = 'inner';
    }
  }
}
avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented
Version: 6.x-1.2 » 6.x-1.x-dev
Status: Needs review » Closed (outdated)

I am closing this issue, as it's for a not supported project version.