Answers.Body: <p>MySQL is forced to generate temporary tables when the query contains GROUP_CONCAT (among other reasons), which this query does in order to capture all values of groups, tags and notes into single fields. The huge temporary tables are the predictable result of several exported fields each having many rows per exported contact, where the those numbers multiply rather than add to achieve the high row count in the temporary table.</p>
  
  <p>Restructuring the query to use subqueries in the critical joins resulted in a speed improvement of 170X!</p>
  
  <p>Additional information <a href="https://lab.civicrm.org/dev/core/-/issues/1725" rel="nofollow noreferrer">here</a>.</p>
  
  <p>Thanks all for your help!</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-22T01:43:12.467Z
Answers.LastActivityDate: 2020-04-22T01:43:12.467Z
Answers.UserDisplayName: BobS
Answers.Body: <ol>
  <li><p>That's a lot of rows examined, a red flag. Perhaps you could try it on a non-smart group instead?</p></li>
  <li><p>I'll guess you're doing the default export. Especially with some older CiviCRM versions, that generated much larger exports that you'd expect or want. Try specifying some specific fields instead and see if that runs much faster.</p></li>
  <li><p>Do tell what version of CiviCRM you're using.</p></li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-09T18:48:07.537Z
Answers.Text: The query for a  non-smart group is almost identical. I've broken down that query by table and contacts. See "EDIT 4/9/20" in the OP. It's definitely dependent on the fields that are being exported, but no field stands out as causing the problem. Using Civi 5.24.3.
Answers.UserDisplayName: BobS
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-09T19:36:06.720Z
Answers.Text: Did you try picking a set of fields to export instead of the default? Also, what do you make of the 90M+ rows being examined?
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-10T16:49:22.527Z
Answers.Text: The "EDIT 4/9/20" section illustrates what happens as fields are added. If a contact has an exported field of X rows and another of field of Y rows, there is a Cartesian join which results in X*Y rows examined. Lots of such fields, each with lots of such rows, and it grows quickly. But for some fields it seems to grow more than it should, and for others the sum of each contact's rows examined is far less than the rows examined for all of the contacts together. Perhaps this is due to the limitations of available memory. For the case of 32,064,083 rows examined, there were 10 merge passes.
Answers.UserDisplayName: BobS
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-07T20:08:25.020Z
Answers.LastActivityDate: 2020-04-07T20:08:25.020Z
Answers.UserDisplayName: Alan Dixon
Answers.Body: <p>Ok, I'm all out of ideas on this I'm afraid. I would probably try a few different export formats to see if its a particular one thats doing it. Check for any extensions - turn them off etc etc. Haven't seen this on a standard install.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-09T18:45:29.747Z
Answers.Text: I've broken down the query by table and contacts. See "EDIT 4/9/20" in the OP.
Answers.UserDisplayName: BobS
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-04-07T20:01:15.347Z
Answers.LastActivityDate: 2020-04-07T20:01:15.347Z
Answers.UserDisplayName: Parvez Saleh
Body: <p>I was alerted to this problem when a contact export was performed that maxed out the 40GB of spare disk space and crashed the entire server.</p>
  
  <p>I found that exporting even a small number of contacts from a smart group is extremely slow and causes MariaDB to use a huge amount of disk space for temporary files.</p>
  
  <p>Specifically, my testing showed that exporting 70 contacts using the default output fields takes 9.4 minutes and creates an 11 GB temporary file.</p>
  
  <p>CiviCRM operations other than exporting are running at reasonable speed.    </p>
  
  <p>The single query of interest (shown below) was run with various numbers of exported contacts to obtain the following measurements:</p>
  
  <pre><code>Contacts   Time (S)         Rows       Temp File
  Exported                 Examined        (GB)
      1       0.24             ?             ?
      5        4.0             ?             ?
     10        4.4             ?             ?
     15         40          8,753,893       1.0
     20         92         18,432,164       2.4
     25        103         21,884,677       2.7
     30        136         25,507,988       3.1
     40        325         44,676,670       5.0
     50        342         52,419,343       5.8
     60        593         92,125,429      11.0*
     70        565         95,407,449      11.0*
  </code></pre>
  
  <p>* Yes, I confirmed the last two cases. It’s not clear why there was little difference between 60 and 70 contacts, but each added contact has its own unique effect based on its number of groups, tags, notes, etc.</p>
  
  <h2>Questions:</h2>
  
  <ul>
  <li>Does this seem normal for a database of this size (see row counts below)?</li>
  <li>Might it be due to a poorly optimized query?</li>
  <li>Does it suggest a MySQL configuration issue?</li>
  <li>Any suggestions?</li>
  </ul>
  
  <h2>Slow query log results for the case of 70 contacts:</h2>
  
  <pre><code>Query_time: 845.694403  Lock_time: 0.341576  Rows_sent: 70  Rows_examined: 95407449
  Full_scan: No  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: Yes
  Filesort: Yes  Filesort_on_disk: No  Merge_passes: 7  Priority_queue: No
  </code></pre>
  
  <h2>Methodology:</h2>
  
  <ul>
  <li>Performed CiviCRM export with default output fields of all 70 contacts in a smart group.</li>
  <li>MySQL slow query log was used to find the offending query.</li>
  <li>That query was then executed repeatedly in MySQL workbench with various
  numbers of requested contact_id’s.</li>
  <li>MySQL slow query log was used to determine “Rows Examined”</li>
  <li>Temp file size was monitored with <code>watch ls -alh /tmp/*.MA?</code></li>
  <li>Times shown are for the query only (excluding fetch times of up to
  340 seconds) as reported by MySQL Workbench</li>
  </ul>
  
  <h2>Server:</h2>
  
  <ul>
  <li>2 core VPS, 8GB RAM, SSD</li>
  <li>MariaDB 10.0</li>
  <li>Ubuntu 16.04</li>
  <li>Drupal 7</li>
  <li>CiviCRM: 5.23.4</li>
  </ul>
  
  <h2>Row counts in critical CiviCRM tables:</h2>
  
  <ul>
  <li>civicrm_contact: 24,206</li>
  <li>civicrm_group_contact_cache: 129,211</li>
  <li>civicrm_group: 367</li>
  <li>civicrm_group_contact: 93,572</li>
  <li>civicrm_tag: 33</li>
  <li>civicrm_entity_tag: 12,761</li>
  <li>civicrm_note: 5,349</li>
  </ul>
  
  <h2>Critical MariaDB/MySQL parameters:</h2>
  
  <ul>
  <li>sort_buffer_size = 8M</li>
  <li>join_buffer_size = 8M</li>
  <li>innodb_buffer_pool_size = 4096M</li>
  <li>key_buffer_size = 128M</li>
  <li>table_open_cache = 512</li>
  </ul>
  
  <h2>Query:</h2>
  
  <pre><code>SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, contact_a.display_name as `display_name`, contact_a.do_not_email as `do_not_email`, contact_a.do_not_phone as `do_not_phone`, contact_a.do_not_mail as `do_not_mail`, contact_a.do_not_sms as `do_not_sms`, contact_a.do_not_trade as `do_not_trade`, contact_a.is_opt_out as `is_opt_out`, contact_a.legal_identifier as `legal_identifier`, contact_a.external_identifier as `external_identifier`, contact_a.nick_name as `nick_name`, contact_a.legal_name as `legal_name`, contact_a.image_URL as `image_URL`, contact_a.preferred_communication_method as `preferred_communication_method`, contact_a.preferred_language as `preferred_language`, contact_a.preferred_mail_format as `preferred_mail_format`, contact_a.hash as `hash`, contact_a.source as `contact_source`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.prefix_id as `prefix_id`, contact_a.suffix_id as `suffix_id`, contact_a.formal_title as `formal_title`, contact_a.communication_style_id as `communication_style_id`, contact_a.email_greeting_id as email_greeting_id, contact_a.postal_greeting_id as postal_greeting_id, contact_a.addressee_id as addressee_id, contact_a.job_title as `job_title`, contact_a.gender_id as `gender_id`, contact_a.birth_date as `birth_date`, contact_a.is_deceased as `is_deceased`, contact_a.deceased_date as `deceased_date`, contact_a.household_name as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, contact_a.sic_code as `sic_code`, contact_a.user_unique_id as `user_unique_id`, contact_a.employer_id as `current_employer_id`, contact_a.is_deleted as `contact_is_deleted`, contact_a.created_date as `created_date`, contact_a.modified_date as `modified_date`, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, contact_a.email_greeting_display as email_greeting_display, contact_a.email_greeting_custom as email_greeting_custom, contact_a.postal_greeting_display as postal_greeting_display, contact_a.postal_greeting_custom as postal_greeting_custom, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_location_type.id as location_type_id, civicrm_location_type.name as `location_type`, civicrm_address.street_address as `street_address`, civicrm_address.street_number as `street_number`, civicrm_address.street_number_suffix as `street_number_suffix`, civicrm_address.street_name as `street_name`, civicrm_address.street_unit as `street_unit`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.supplemental_address_3 as `supplemental_address_3`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_address.manual_geo_code as `manual_geo_code`, civicrm_address.name as `address_name`, civicrm_address.master_id as `master_id`, civicrm_address.county_id as county_id, civicrm_address.state_province_id as state_province_id, civicrm_address.country_id as country_id, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as `phone_type_id`, civicrm_phone.phone as `phone`, civicrm_phone.phone_ext as `phone_ext`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_email.is_bulkmail as `is_bulkmail`, civicrm_email.signature_text as `signature_text`, civicrm_email.signature_html as `signature_html`, civicrm_im.id as im_id, civicrm_im.provider_id as `im_provider`, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_openid.id as openid_id, civicrm_openid.openid as `openid`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`, civicrm_website.id as website_id, civicrm_website.url as `url`, 
    CONCAT_WS(',',
      GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
      GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
    )
  as `groups`, GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags, GROUP_CONCAT(DISTINCT(civicrm_note.note)) as notes  FROM civicrm_contact contact_a   LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id  )  LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id )  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id )  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id )  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id )  LEFT JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id  )  LEFT JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id  LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id  LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = contact_a.id )  LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id )  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id   LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_5e8691eef0e92 ON contact_a.id = civicrm_group_contact_cache_5e8691eef0e92.contact_id   LEFT  JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id  LEFT JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id  WHERE  (  (  ( civicrm_group_contact_cache_5e8691eef0e92.group_id IN ("381") )  )  )  AND (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1 AND  contact_a.id IN ( 6442,22905,990,17543,6317,614,21675,22232,543,5161,5532,19913,593,1016,25226,12381,3,2,4596,19253,574,8641,14216,757,8263,5219,22235,16554,14028,2276,17450,21002,555,21312,8076,5191,10562,12706,11653,760,8113,315,2293,1369,765,19581,1303,4893,21344,1907,1814,771,21299,25138,1819,22464,22818,8553,795,10568,925,1296,5557,17018,911,17932,750,21552,10364,11819 )    GROUP BY contact_a.id LIMIT 0, 100000;
  </code></pre>
  
  <h2>Explain:</h2>
  
  <pre><code>id  select_type  table                                      type     possible_keys                                             key                               key_len   ref                                                              rows  Extra
  1   SIMPLE       civicrm_group_contact_cache_5e8691eef0e92  range    UI_contact_group,FK_civicrm_group_contact_cache_group_id  UI_contact_group                  8         NULL                                                             70    Using where; Using index; Using temporary; Using filesort
  1   SIMPLE       contact_a                                  eq_ref   PRIMARY,index_is_deleted_sort_name                        PRIMARY                           4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     Using where
  1   SIMPLE       civicrm_address                            ref      FK_civicrm_address_contact_id                             FK_civicrm_address_contact_id     5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  1   SIMPLE       civicrm_country                            eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_address.country_id                                   1     Using where
  1   SIMPLE       civicrm_email                              ref      FK_civicrm_email_contact_id                               FK_civicrm_email_contact_id       5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  1   SIMPLE       civicrm_phone                              ref      FK_civicrm_phone_contact_id                               FK_civicrm_phone_contact_id       5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  1   SIMPLE       civicrm_im                                 ref      FK_civicrm_im_contact_id                                  FK_civicrm_im_contact_id          5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  1   SIMPLE       civicrm_openid                             ALL      FK_civicrm_openid_contact_id                              NULL                              NULL      NULL                                                             1     Using where
  1   SIMPLE       civicrm_location_type                      eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_address.location_type_id                             1     Using where
  1   SIMPLE       civicrm_group_contact                      ref      UI_contact_group                                          UI_contact_group                  4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         2     
  1   SIMPLE       civicrm_group_contact_cache                ref      UI_contact_group                                          UI_contact_group                  4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         2     Using index
  1   SIMPLE       civicrm_entity_tag                         ref      UI_entity_id_entity_table_tag_id                          UI_entity_id_entity_table_tag_id  199       sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id,const   1     Using where; Using index
  1   SIMPLE       civicrm_note                               ref      index_entity                                              index_entity                      198       const,sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id   1     Using where
  1   SIMPLE       civicrm_worldregion                        eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_country.region_id                                    1     Using where
  1   SIMPLE       civicrm_tag                                eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_entity_tag.tag_id                                    1     Using where
  1   SIMPLE       civicrm_website                            ref      FK_civicrm_website_contact_id                             FK_civicrm_website_contact_id     5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  </code></pre>
  
  <p><br><br></p>
  
  <hr>
  
  <p><br><br></p>
  
  <p><strong>EDIT 4/9/20</strong>
  The following is based on the query that occurs during a regular (non-smart) group export of 2 contacts. It's very similar to the above smart-group query. The table below shows the results of that single query for contact #1 alone, contact #2 alone, and both contacts together, as each table is added to the SELECT and JOIN sections.</p>
  
  <pre><code>Exported Contact(s)                             | ---------------#1---------------| -------------#2--------------- | ------------------#1 &amp; #2----------------- |
                                                  | Count  Rows Examined   Time (S) | Count  Rows Examined  Time (S) | Rows Examined Sum  Rows Examined  Time (S) |
  Full query (from Export from reg. group)        |        13,170,619      44       |        5,258,678      16       | 18,429,297         32,064,083     349      |
  SELECT contact_id only (no joins)               |        1               0.1      |        1              0.1      | 2                  2              0.188    |
    + group_contact (regular groups)              | 81     89              0.1      | 36     38             0.1      | 127                129            0.1      |
      + group_tag (smart groups)                  | 32     2,937           0.18     | 21     836            0.1      | 3,773              3,775          0.1      |
        + tag, entity_tag                         | 18     105,465         0.29     | 6      10,412         0.14     | 115,877            115,879        0.39     |
          + notes                                 | 6      669,369         4.1      | 11     110,960        0.48     | 780,329            780,331        3        |
            + email                               | 4      2,677,480       11.5     | 3      332,883        1.2      | 3,010,363          3,010,365      10.8     |
              + phone                             | 2      5,354,964       19.5     | 4      1,331,535      4.6      | 6,686,499          6,686,501      23.8     |
                + address, country, location_type | 2      10,709,930      40.8     | 3      3,994,608      13.5     | 14,704,538         14,704,540     51.5     |
                  + open_id                       | 0      10,709,946      38       | 0      3,994,644      13.2     | 14,704,590         28,339,376     136      |
                  + im                            | 0      10,709,946      38       | 0      3,994,644      13.5     | 14,704,590         28,339,376     136      |
                  + im, open_id                   | 0      10,709,946      37.8     | 0      3,994,644      13.5     | 14,704,590         28,339,376     103      |
                     + world_region               | 1      13,170,619      39.7     | 1      5,258,678      13.8     | 18,429,297         32,064,083     145.5    |
  
  </code></pre>
  
  <p>For example:</p>
  
  <ul>
  <li>The "+ email" row indicates the query results including (in Select and Joins) email, notes, and all other tables above it.</li>
  <li>The "+ im" row indicates the query results including address, phone, etc., but excludes the open_id table as that row has the same indentation.</li>
  </ul>
  
  <p>Points of interest:</p>
  
  <ul>
  <li>The column "Rows Examined Sum" is simply the sum of Rows Examined for Contacts 1 and 2. This is generally very close to the actual Rows Examined for the query run with both contacts, but in some cases the latter is much greater. Specifically, when the open_id or im tables are introduced, there is little effect on the Rows Examined for either contact #1 or contact #2, but for the case of #1 AND #2, there is a large difference! Oddly, neither contact has a row in either of those two tables, and the total row counts of these two tables is only 0 and 6, respectively.</li>
  <li>The last row, "+ world_region" adds a considerable amount to Rows Examined, although each contact has only one unique country in all of their address records (and therefore only one world region). </li>
  <li>The increases in execution time are disproportionally greater than the increases in Rows Examined. .e.g for "+ world_region", the Records Examined for the case of #1 AND #2 increased by 13%, while the time to execute increased by 41%! Perhaps this is due to greater reliance on temporary tables on disk.</li>
  </ul>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-03T15:04:18.827Z
Comments.Text: Hi - Could you attach an explain plan?
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-03T20:52:08.047Z
Comments.Text: Explain plan added.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-03T21:30:56.763Z
Comments.Text: Hmm, strange, explain plan looks good and no wild joins going on. Could you provide a bit more information about the database itself, is it a clean import or something thats built up over time? Are you running the GDPR extension?
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-03T21:34:33.577Z
Comments.Text: p.s. the time being taken to export should be a few seconds, we have databases with over a million contact records and they perform within a few seconds so something is definitely wrong
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-03T21:36:39.580Z
Comments.Text: And my final thought for today is have you tried running performance tuning on the server? Something along the lines of https://github.com/major/MySQLTuner-perl.
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-04T21:31:34.590Z
Comments.Text: Thanks for your help, Parvez. The DB has been in service since 2014. I’m not running GPDR extension. At your suggestion, I tried MySQLTuner, repeatedly modifying parameters, restarting MySQL, running the report, running the “big” query, and running the report again. Immediately after restart, MySQLTuner tells me to increase innodb_buffer_pool_size > 2GB, so I set that to 3GB and it it stopped complaining. After running the query at issue, MySQLTuner says “join_buffer_size (> XXX, or always use indexes with JOINs)”.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-04T21:31:50.867Z
Comments.Text: I walked join_buffer_size all the way up from 128K to 512M, but at that point it’s warning of a ridiculous RAM requirement of 81.7GB (based on Total buffers = 3.4G global + 530.6M per thread x 151 max threads), and still telling me that’s not enough. None of the changes I made had a significant effect on the execution speed of the query at issue. I’m back where I started, wondering why this query requires a crazy amount of memory.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-04T21:40:20.843Z
Comments.Text: Ok, One thing to try is clone the database and see if you have the same issue on the clone. It may be a fragmentation issue, sometimes resolved by exporting the database, dropping it and reimporting. But to test the theory you could clone the site and see if you have the same issue on the clone. If you do then its probably not fragmentation.
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-04T22:59:57.497Z
Comments.Text: Same issue with the clone.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-05T00:05:40.167Z
Comments.Text: Ok, this is definitely all a bit weird. I'm wondering if theres a bug/issue been introduced in the most recent version - your running a pretty recent version. As mentioned we have installs with much bigger numbers as well as around your size and not seen this. One thing thats not adding up is your explain plan shows a max of 70 rows interrogated but your slow log results show 87k rows examined so theres something not being reported or the issue is with the build of the group temp table. Maybe thats where the query is slow and an explain there might help?
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-05T00:33:44.723Z
Comments.Text: My OP test results were based on executing that single query, so that excludes any time required to rebuild the group temp table.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-05T00:50:40.060Z
Comments.Text: I just tried a similar export operation of 76 contacts (from a similarly populated database) using CiviCRM 5.15.1. The temp file got up to 16GB in 5 minutes before I killed it.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-07T20:46:35.960Z
Comments.Text: Check that the smart group is not corrupted.  We have noted with recent upgrades that smart groups can get munged and need to be rebuilt.  Be sure to refresh the smart group cache as well.  (The Explain says that temporary was used for civicrm_group_contact_cache_5e8691eef0e92  so that's my guess.)
Comments.UserDisplayName: DaveD
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-09T18:49:05.167Z
Comments.Text: I've broken down the query by table and contacts. See "EDIT 4/9/20" in the OP. Smart groups contribute to the problem, but so do regular groups and other fields.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-10T02:04:47.470Z
Comments.Text: On the reruns, were temp tables used?  Where?
Comments.UserDisplayName: DaveD
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-10T16:52:37.903Z
Comments.Text: For the case of 32,064,083 rows examined, there were 10 merge passes. Temp tables on disk. File sort not on disk.
Comments.UserDisplayName: BobS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-04-22T16:02:19.830Z
Comments.Text: We had the same issue. As a workaround we disabled the ability to use the default set of fields in a form hook. More limited sets of fields seem to work OK.
Comments.UserDisplayName: lolcode
Tags: <export><performance>
Title: Contact export runs very slowly, creates huge temporary DB files
ThreadText: # Contact export runs very slowly, creates huge temporary DB files
  
  (Asked by: username_0 on 2020-04-03)
  
  I was alerted to this problem when a contact export was performed that maxed out the 40GB of spare disk space and crashed the entire server.
  I found that exporting even a small number of contacts from a smart group is extremely slow and causes MariaDB to use a huge amount of disk space for temporary files.
  Specifically, my testing showed that exporting 70 contacts using the default output fields takes 9.4 minutes and creates an 11 GB temporary file.
  CiviCRM operations other than exporting are running at reasonable speed.    
  The single query of interest (shown below) was run with various numbers of exported contacts to obtain the following measurements:
  Contacts   Time (S)         Rows       Temp File
  Exported                 Examined        (GB)
      1       0.24             ?             ?
      5        4.0             ?             ?
     10        4.4             ?             ?
     15         40          8,753,893       1.0
     20         92         18,432,164       2.4
     25        103         21,884,677       2.7
     30        136         25,507,988       3.1
     40        325         44,676,670       5.0
     50        342         52,419,343       5.8
     60        593         92,125,429      11.0*
     70        565         95,407,449      11.0*
  
  * Yes, I confirmed the last two cases. It’s not clear why there was little difference between 60 and 70 contacts, but each added contact has its own unique effect based on its number of groups, tags, notes, etc.
  Questions:
  
  Does this seem normal for a database of this size (see row counts below)?
  Might it be due to a poorly optimized query?
  Does it suggest a MySQL configuration issue?
  Any suggestions?
  
  Slow query log results for the case of 70 contacts:
  Query_time: 845.694403  Lock_time: 0.341576  Rows_sent: 70  Rows_examined: 95407449
  Full_scan: No  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: Yes
  Filesort: Yes  Filesort_on_disk: No  Merge_passes: 7  Priority_queue: No
  
  Methodology:
  
  Performed CiviCRM export with default output fields of all 70 contacts in a smart group.
  MySQL slow query log was used to find the offending query.
  That query was then executed repeatedly in MySQL workbench with various
  numbers of requested contact_id’s.
  MySQL slow query log was used to determine “Rows Examined”
  Temp file size was monitored with watch ls -alh /tmp/*.MA?
  Times shown are for the query only (excluding fetch times of up to
  340 seconds) as reported by MySQL Workbench
  
  Server:
  
  2 core VPS, 8GB RAM, SSD
  MariaDB 10.0
  Ubuntu 16.04
  Drupal 7
  CiviCRM: 5.23.4
  
  Row counts in critical CiviCRM tables:
  
  civicrm_contact: 24,206
  civicrm_group_contact_cache: 129,211
  civicrm_group: 367
  civicrm_group_contact: 93,572
  civicrm_tag: 33
  civicrm_entity_tag: 12,761
  civicrm_note: 5,349
  
  Critical MariaDB/MySQL parameters:
  
  sort_buffer_size = 8M
  join_buffer_size = 8M
  innodb_buffer_pool_size = 4096M
  key_buffer_size = 128M
  table_open_cache = 512
  
  Query:
  SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, contact_a.display_name as `display_name`, contact_a.do_not_email as `do_not_email`, contact_a.do_not_phone as `do_not_phone`, contact_a.do_not_mail as `do_not_mail`, contact_a.do_not_sms as `do_not_sms`, contact_a.do_not_trade as `do_not_trade`, contact_a.is_opt_out as `is_opt_out`, contact_a.legal_identifier as `legal_identifier`, contact_a.external_identifier as `external_identifier`, contact_a.nick_name as `nick_name`, contact_a.legal_name as `legal_name`, contact_a.image_URL as `image_URL`, contact_a.preferred_communication_method as `preferred_communication_method`, contact_a.preferred_language as `preferred_language`, contact_a.preferred_mail_format as `preferred_mail_format`, contact_a.hash as `hash`, contact_a.source as `contact_source`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.prefix_id as `prefix_id`, contact_a.suffix_id as `suffix_id`, contact_a.formal_title as `formal_title`, contact_a.communication_style_id as `communication_style_id`, contact_a.email_greeting_id as email_greeting_id, contact_a.postal_greeting_id as postal_greeting_id, contact_a.addressee_id as addressee_id, contact_a.job_title as `job_title`, contact_a.gender_id as `gender_id`, contact_a.birth_date as `birth_date`, contact_a.is_deceased as `is_deceased`, contact_a.deceased_date as `deceased_date`, contact_a.household_name as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, contact_a.sic_code as `sic_code`, contact_a.user_unique_id as `user_unique_id`, contact_a.employer_id as `current_employer_id`, contact_a.is_deleted as `contact_is_deleted`, contact_a.created_date as `created_date`, contact_a.modified_date as `modified_date`, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, contact_a.email_greeting_display as email_greeting_display, contact_a.email_greeting_custom as email_greeting_custom, contact_a.postal_greeting_display as postal_greeting_display, contact_a.postal_greeting_custom as postal_greeting_custom, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_location_type.id as location_type_id, civicrm_location_type.name as `location_type`, civicrm_address.street_address as `street_address`, civicrm_address.street_number as `street_number`, civicrm_address.street_number_suffix as `street_number_suffix`, civicrm_address.street_name as `street_name`, civicrm_address.street_unit as `street_unit`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.supplemental_address_3 as `supplemental_address_3`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_address.manual_geo_code as `manual_geo_code`, civicrm_address.name as `address_name`, civicrm_address.master_id as `master_id`, civicrm_address.county_id as county_id, civicrm_address.state_province_id as state_province_id, civicrm_address.country_id as country_id, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as `phone_type_id`, civicrm_phone.phone as `phone`, civicrm_phone.phone_ext as `phone_ext`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_email.is_bulkmail as `is_bulkmail`, civicrm_email.signature_text as `signature_text`, civicrm_email.signature_html as `signature_html`, civicrm_im.id as im_id, civicrm_im.provider_id as `im_provider`, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_openid.id as openid_id, civicrm_openid.openid as `openid`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`, civicrm_website.id as website_id, civicrm_website.url as `url`, 
    CONCAT_WS(',',
      GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
      GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
    )
  as `groups`, GROUP_CONCAT(DISTINCT(civicrm_tag.name)) as tags, GROUP_CONCAT(DISTINCT(civicrm_note.note)) as notes  FROM civicrm_contact contact_a   LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id  )  LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id )  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id )  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id )  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id )  LEFT JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id  )  LEFT JOIN civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id  LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id  LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = contact_a.id )  LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id )  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id   LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_5e8691eef0e92 ON contact_a.id = civicrm_group_contact_cache_5e8691eef0e92.contact_id   LEFT  JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id  LEFT JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id  WHERE  (  (  ( civicrm_group_contact_cache_5e8691eef0e92.group_id IN ("381") )  )  )  AND (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1 AND  contact_a.id IN ( 6442,22905,990,17543,6317,614,21675,22232,543,5161,5532,19913,593,1016,25226,12381,3,2,4596,19253,574,8641,14216,757,8263,5219,22235,16554,14028,2276,17450,21002,555,21312,8076,5191,10562,12706,11653,760,8113,315,2293,1369,765,19581,1303,4893,21344,1907,1814,771,21299,25138,1819,22464,22818,8553,795,10568,925,1296,5557,17018,911,17932,750,21552,10364,11819 )    GROUP BY contact_a.id LIMIT 0, 100000;
  
  Explain:
  id  select_type  table                                      type     possible_keys                                             key                               key_len   ref                                                              rows  Extra
  1   SIMPLE       civicrm_group_contact_cache_5e8691eef0e92  range    UI_contact_group,FK_civicrm_group_contact_cache_group_id  UI_contact_group                  8         NULL                                                             70    Using where; Using index; Using temporary; Using filesort
  1   SIMPLE       contact_a                                  eq_ref   PRIMARY,index_is_deleted_sort_name                        PRIMARY                           4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     Using where
  1   SIMPLE       civicrm_address                            ref      FK_civicrm_address_contact_id                             FK_civicrm_address_contact_id     5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  1   SIMPLE       civicrm_country                            eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_address.country_id                                   1     Using where
  1   SIMPLE       civicrm_email                              ref      FK_civicrm_email_contact_id                               FK_civicrm_email_contact_id       5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  1   SIMPLE       civicrm_phone                              ref      FK_civicrm_phone_contact_id                               FK_civicrm_phone_contact_id       5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  1   SIMPLE       civicrm_im                                 ref      FK_civicrm_im_contact_id                                  FK_civicrm_im_contact_id          5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  1   SIMPLE       civicrm_openid                             ALL      FK_civicrm_openid_contact_id                              NULL                              NULL      NULL                                                             1     Using where
  1   SIMPLE       civicrm_location_type                      eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_address.location_type_id                             1     Using where
  1   SIMPLE       civicrm_group_contact                      ref      UI_contact_group                                          UI_contact_group                  4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         2     
  1   SIMPLE       civicrm_group_contact_cache                ref      UI_contact_group                                          UI_contact_group                  4         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         2     Using index
  1   SIMPLE       civicrm_entity_tag                         ref      UI_entity_id_entity_table_tag_id                          UI_entity_id_entity_table_tag_id  199       sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id,const   1     Using where; Using index
  1   SIMPLE       civicrm_note                               ref      index_entity                                              index_entity                      198       const,sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id   1     Using where
  1   SIMPLE       civicrm_worldregion                        eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_country.region_id                                    1     Using where
  1   SIMPLE       civicrm_tag                                eq_ref   PRIMARY                                                   PRIMARY                           4         sb1.civicrm_entity_tag.tag_id                                    1     Using where
  1   SIMPLE       civicrm_website                            ref      FK_civicrm_website_contact_id                             FK_civicrm_website_contact_id     5         sb1.civicrm_group_contact_cache_5e8691eef0e92.contact_id         1     
  
  
  
  
  EDIT 4/9/20
  The following is based on the query that occurs during a regular (non-smart) group export of 2 contacts. It's very similar to the above smart-group query. The table below shows the results of that single query for contact #1 alone, contact #2 alone, and both contacts together, as each table is added to the SELECT and JOIN sections.
  Exported Contact(s)                             | ---------------#1---------------| -------------#2--------------- | ------------------#1 & #2----------------- |
                                                  | Count  Rows Examined   Time (S) | Count  Rows Examined  Time (S) | Rows Examined Sum  Rows Examined  Time (S) |
  Full query (from Export from reg. group)        |        13,170,619      44       |        5,258,678      16       | 18,429,297         32,064,083     349      |
  SELECT contact_id only (no joins)               |        1               0.1      |        1              0.1      | 2                  2              0.188    |
    + group_contact (regular groups)              | 81     89              0.1      | 36     38             0.1      | 127                129            0.1      |
      + group_tag (smart groups)                  | 32     2,937           0.18     | 21     836            0.1      | 3,773              3,775          0.1      |
        + tag, entity_tag                         | 18     105,465         0.29     | 6      10,412         0.14     | 115,877            115,879        0.39     |
          + notes                                 | 6      669,369         4.1      | 11     110,960        0.48     | 780,329            780,331        3        |
            + email                               | 4      2,677,480       11.5     | 3      332,883        1.2      | 3,010,363          3,010,365      10.8     |
              + phone                             | 2      5,354,964       19.5     | 4      1,331,535      4.6      | 6,686,499          6,686,501      23.8     |
                + address, country, location_type | 2      10,709,930      40.8     | 3      3,994,608      13.5     | 14,704,538         14,704,540     51.5     |
                  + open_id                       | 0      10,709,946      38       | 0      3,994,644      13.2     | 14,704,590         28,339,376     136      |
                  + im                            | 0      10,709,946      38       | 0      3,994,644      13.5     | 14,704,590         28,339,376     136      |
                  + im, open_id                   | 0      10,709,946      37.8     | 0      3,994,644      13.5     | 14,704,590         28,339,376     103      |
                     + world_region               | 1      13,170,619      39.7     | 1      5,258,678      13.8     | 18,429,297         32,064,083     145.5    |
  
  
  For example:
  
  The "+ email" row indicates the query results including (in Select and Joins) email, notes, and all other tables above it.
  The "+ im" row indicates the query results including address, phone, etc., but excludes the open_id table as that row has the same indentation.
  
  Points of interest:
  
  The column "Rows Examined Sum" is simply the sum of Rows Examined for Contacts 1 and 2. This is generally very close to the actual Rows Examined for the query run with both contacts, but in some cases the latter is much greater. Specifically, when the open_id or im tables are introduced, there is little effect on the Rows Examined for either contact #1 or contact #2, but for the case of #1 AND #2, there is a large difference! Oddly, neither contact has a row in either of those two tables, and the total row counts of these two tables is only 0 and 6, respectively.
  The last row, "+ world_region" adds a considerable amount to Rows Examined, although each contact has only one unique country in all of their address records (and therefore only one world region). 
  The increases in execution time are disproportionally greater than the increases in Rows Examined. .e.g for "+ world_region", the Records Examined for the case of #1 AND #2 increased by 13%, while the time to execute increased by 41%! Perhaps this is due to greater reliance on temporary tables on disk.
  
  **Tags:** export, performance
  
  > **Comments:**
  > **username_1**: Hi - Could you attach an explain plan?  
  > **username_0**: Explain plan added.  
  > **username_1**: Hmm, strange, explain plan looks good and no wild joins going on. Could you provide a bit more information about the database itself, is it a clean import or something thats built up over time? Are you running the GDPR extension?  
  > **username_1**: p.s. the time being taken to export should be a few seconds, we have databases with over a million contact records and they perform within a few seconds so something is definitely wrong  
  > **username_1**: And my final thought for today is have you tried running performance tuning on the server? Something along the lines of https://github.com/major/MySQLTuner-perl.  
  > **username_0**: Thanks for your help, Parvez. The DB has been in service since 2014. I’m not running GPDR extension. At your suggestion, I tried MySQLTuner, repeatedly modifying parameters, restarting MySQL, running the report, running the “big” query, and running the report again. Immediately after restart, MySQLTuner tells me to increase innodb_buffer_pool_size > 2GB, so I set that to 3GB and it it stopped complaining. After running the query at issue, MySQLTuner says “join_buffer_size (> XXX, or always use indexes with JOINs)”.  
  > **username_0**: I walked join_buffer_size all the way up from 128K to 512M, but at that point it’s warning of a ridiculous RAM requirement of 81.7GB (based on Total buffers = 3.4G global + 530.6M per thread x 151 max threads), and still telling me that’s not enough. None of the changes I made had a significant effect on the execution speed of the query at issue. I’m back where I started, wondering why this query requires a crazy amount of memory.  
  > **username_1**: Ok, One thing to try is clone the database and see if you have the same issue on the clone. It may be a fragmentation issue, sometimes resolved by exporting the database, dropping it and reimporting. But to test the theory you could clone the site and see if you have the same issue on the clone. If you do then its probably not fragmentation.  
  > **username_0**: Same issue with the clone.  
  > **username_1**: Ok, this is definitely all a bit weird. I'm wondering if theres a bug/issue been introduced in the most recent version - your running a pretty recent version. As mentioned we have installs with much bigger numbers as well as around your size and not seen this. One thing thats not adding up is your explain plan shows a max of 70 rows interrogated but your slow log results show 87k rows examined so theres something not being reported or the issue is with the build of the group temp table. Maybe thats where the query is slow and an explain there might help?  
  > **username_0**: My OP test results were based on executing that single query, so that excludes any time required to rebuild the group temp table.  
  > **username_0**: I just tried a similar export operation of 76 contacts (from a similarly populated database) using CiviCRM 5.15.1. The temp file got up to 16GB in 5 minutes before I killed it.  
  > **username_2**: Check that the smart group is not corrupted.  We have noted with recent upgrades that smart groups can get munged and need to be rebuilt.  Be sure to refresh the smart group cache as well.  (The Explain says that temporary was used for civicrm_group_contact_cache_5e8691eef0e92  so that's my guess.)  
  > **username_0**: I've broken down the query by table and contacts. See "EDIT 4/9/20" in the OP. Smart groups contribute to the problem, but so do regular groups and other fields.  
  > **username_2**: On the reruns, were temp tables used?  Where?  
  > **username_0**: For the case of 32,064,083 rows examined, there were 10 merge passes. Temp tables on disk. File sort not on disk.  
  > **username_3**: We had the same issue. As a workaround we disabled the ability to use the default set of fields in a form hook. More limited sets of fields seem to work OK.  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_0 on 2020-04-22. Score: 2 | Accepted answer
  
  MySQL is forced to generate temporary tables when the query contains GROUP\_CONCAT (among other reasons), which this query does in order to capture all values of groups, tags and notes into single fields. The huge temporary tables are the predictable result of several exported fields each having many rows per exported contact, where the those numbers multiply rather than add to achieve the high row count in the temporary table.
  
  Restructuring the query to use subqueries in the critical joins resulted in a speed improvement of 170X!
  
  Additional information [here](https://lab.civicrm.org/dev/core/-/issues/1725).
  
  Thanks all for your help!
  
  
  ---
  
  ### Answer by username_4 on 2020-04-07. Score: 0
  
  1. That's a lot of rows examined, a red flag. Perhaps you could try it on a non-smart group instead?
  2. I'll guess you're doing the default export. Especially with some older CiviCRM versions, that generated much larger exports that you'd expect or want. Try specifying some specific fields instead and see if that runs much faster.
  3. Do tell what version of CiviCRM you're using.
  
  > **Comments:**
  > **username_0**: The query for a  non-smart group is almost identical. I've broken down that query by table and contacts. See "EDIT 4/9/20" in the OP. It's definitely dependent on the fields that are being exported, but no field stands out as causing the problem. Using Civi 5.24.3.  
  > **username_4**: Did you try picking a set of fields to export instead of the default? Also, what do you make of the 90M+ rows being examined?  
  > **username_0**: The "EDIT 4/9/20" section illustrates what happens as fields are added. If a contact has an exported field of X rows and another of field of Y rows, there is a Cartesian join which results in X*Y rows examined. Lots of such fields, each with lots of such rows, and it grows quickly. But for some fields it seems to grow more than it should, and for others the sum of each contact's rows examined is far less than the rows examined for all of the contacts together. Perhaps this is due to the limitations of available memory. For the case of 32,064,083 rows examined, there were 10 merge passes.  
  
  ---
  
  ### Answer by username_1 on 2020-04-07. Score: 0
  
  Ok, I'm all out of ideas on this I'm afraid. I would probably try a few different export formats to see if its a particular one thats doing it. Check for any extensions - turn them off etc etc. Haven't seen this on a standard install.
  
  > **Comments:**
  > **username_0**: I've broken down the query by table and contacts. See "EDIT 4/9/20" in the OP.  
  
  ---
  
  