Some reports will open faster if they are built with straight join option enabled. It’s difficult to identify which reports will benefit.
In particular reports that meet one or more of the following criteria are more likely to perform better with Straight Join enabled:
- Large reports with multiple tables
- Reports with a large number of custom fields (more than 12 or 15)
- Reports with Tracking Activities (Level 2) as the primary table.
It is best practice to build reports with those attributes with Straight Join enabled, and then disable Straight Join to evaluate which option has a faster response when viewing the report.
Important: when the Straight Join option is enabled or disabled, the report must be rebuilt before the change will take effect.
Technical Details: * MySQL5 has a process to optimize queries that in some cases actually reduces the performance. This is a known issue with MySQL5. * Enabling the straight join prevents MySQL from trying to optimize the query and runs it exactly as provided by the system.