Answers.Body: <p>So, after talking to colleagues my understanding is...</p>
  <p>61 joins will cause problems on big queries (reports) where we try and pull in all activity types and all associated activity data and additional contact data</p>
  <p>If reports are more focussed then most reports will be within the 61 join limit even with more than 60 associated tables to choose from, adding more tables won't necessarily cause things to break, but caution is needed as the db expands.</p>
  <p>Drupal views and the Data Processor extension can help in that process not just relying on reports.</p>
  <p>What can break is if a custom data table gets too big 38-40 fields plus. And that can stop the entity associated from the data saving.</p>
  <p>Here to learn, so feel free to tell me where I'm wrong with this.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-06-17T08:29:46.623Z
Answers.LastActivityDate: 2021-06-17T08:29:46.623Z
Answers.UserDisplayName: Craig Almond
Body: <p>From experience I know that once a database reaches a certain size the sql limitation of 61 joins can cause problems</p>
  <p>I've  seen it raised in stack exchang here:<a href="https://civicrm.stackexchange.com/questions/20890/too-many-tables-mysql-limitation-civicrm-api-errors">Too many tables - MySQL limitation - CiviCRM API errors</a></p>
  <p>And as a git lab issue: <a href="https://lab.civicrm.org/dev/core/-/issues/1191" rel="nofollow noreferrer">https://lab.civicrm.org/dev/core/-/issues/1191</a></p>
  <p>I'm working on a site with currently 70 active custom data tables (50 for activities, 9 for individuals, 2-4 each for other entities) And am concerned that soon we're going to hit this limit.</p>
  <p>Adding activities isn't a problem because no activity uses all 70 available data sets.</p>
  <p>But will reporting on activities be a problem, because the activity data sets and contact data sets can all be included by default in the report templates. Or are activity reports handled differently now?</p>
  
Tags: <custom-fields><mysql>
Title: SQL too many joins limitations- what will break and how
ThreadText: # SQL too many joins limitations- what will break and how
  
  (Asked by: username_0 on 2021-06-14)
  
  From experience I know that once a database reaches a certain size the sql limitation of 61 joins can cause problems
  
  I've seen it raised in stack exchang here:[Too many tables - MySQL limitation - CiviCRM API errors](https://civicrm.stackexchange.com/questions/20890/too-many-tables-mysql-limitation-civicrm-api-errors)
  
  And as a git lab issue: <https://lab.civicrm.org/dev/core/-/issues/1191>
  
  I'm working on a site with currently 70 active custom data tables (50 for activities, 9 for individuals, 2-4 each for other entities) And am concerned that soon we're going to hit this limit.
  
  Adding activities isn't a problem because no activity uses all 70 available data sets.
  
  But will reporting on activities be a problem, because the activity data sets and contact data sets can all be included by default in the report templates. Or are activity reports handled differently now?
  
  **Tags:** custom-fields, mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2021-06-17. Score: 1
  
  So, after talking to colleagues my understanding is...
  
  61 joins will cause problems on big queries (reports) where we try and pull in all activity types and all associated activity data and additional contact data
  
  If reports are more focussed then most reports will be within the 61 join limit even with more than 60 associated tables to choose from, adding more tables won't necessarily cause things to break, but caution is needed as the db expands.
  
  Drupal views and the Data Processor extension can help in that process not just relying on reports.
  
  What can break is if a custom data table gets too big 38-40 fields plus. And that can stop the entity associated from the data saving.
  
  Here to learn, so feel free to tell me where I'm wrong with this.
  
  
  ---
  
  