Crystal reports and MySQL - unable to use left outer join

Question

I have recently installed Crystal reports 2011 version 14.0.2.364 RTM. I am connecting to a MySQL database having created a DSN using the MySQL ODBC 3.51 driver. I am able to create reports using individual tables or right joins, however, when I try creating a report using a left join Crystal freezes and I have to force the program to quit.

The timeout settings on MySQL server are set to 30 minutes so this does not appear to be the issue. Using Microsoft SQL Server Management Studio and accessing the same database as a linked server the query takes 1 minutes 18 seconds to return 1,380,993 rows.

I cannot work out why Crystal is unable to perform the query/return results when the SQL query executes fine. Please let me know if you need any further information.

query in crystal

 SELECT `applications1`.`id`, `application_statuses1`.`status`
 FROM   `js_reporting`.`applications` `applications1`
  LEFT OUTER JOIN `js_reporting`.`application_statuses` `application_statuses1` ON `applications1`.`id`=`application_statuses1`.`application_id`

same query in SQL server

select applications.id, application_statuses.status
from mysql_js...applications
 left join mysql_js...application_statuses on applications.id=application_statuses.application_id

Can I asked if you have any indexes set up on your MySQL DB or your? With such a large number of records, I think the lack of an index is the first thing that jumps out at me. — Alec.
Our developers assure me indexes are both setup and working correctly — user2694655
Hi Alec - After further discussion and investigation with our developers, it was in fact a lack of appropriate indexing and the splitting of a set of data into 2 tables which was causing the issue, thanks for your help. — user2694655

user2694655 user2694655 · Accepted Answer · 2013-09-16T21:20:34

The issue was essentially due to a lack of indexing (thanks to Alec Henderson for raising this). Also our developers had split one of our tables of into two separate tables so the database was struggling to deal with this without a linking table between the 2 split tables and subsequent linked tables.

Crystal reports and MySQL - unable to use left outer join

2 Answers