I am having trouble with running this query. On my personal computer I had upgraded MySQL to 5.7 to do some better logging and this query works just fine. When I put it into production on 5.5 it works, but only sometimes.

Using HeidiSQL it works but takes 50 seconds to run (only takes 2.6 seconds on my PC using the same data). Using my C# app, I get an error "Fatal error encountered during command execution". Any idea what is different about those two situations that it would work in one but not the other? Does the execution time have any bearing on it?

I found a number of problems and solutions. Since I didn't hear anything from here, I posted on StackOverflow (https://stackoverflow.com/questions/498 ... in-c-sharp) and got some help. The issue was that it was taking about 60 seconds to run, and so it would time out. I had to change the timeout by setting `cmd.CommandTimeout = 0;` to allow it to finish.

The interesting part is that I had originally gotten parts of my query from:"OpenDental\opendental17.4\OpenDentBusiness\Db Multi Table\Ledgers.cs"

I was able to simplify the query quite a bit (down to only 187 lines) by simply using the balances from the patient table so that it runs in about 3 seconds on the production Mysql 5.5 machine, but the next part is the most interesting to me. Researching the differences in 5.5 Vs 5.7, I came across this link: https://stackoverflow.com/questions/489 ... -mysql-5-7 In particular what grabbed my attention was this:

MySQL 5.5 - EXISTS - 53 secondsMySQL 5.5 - IN - 48 seconds

MySQL 5.7 - EXISTS - 46 secondsMySQL 5.7 - IN - 4 seconds

Seems like it would be simpler to upgrade the distributed version of Mysql which could probably be done with a single developer, rather than to go back and rewrite all the queries to be more optimized for 5.5 which would take much more manpower. Since 5.7 has been out since 2015-10-21, what are we waiting for? I saw on this thread from a year and a half ago that you were working on 5.6 - are we going to step into 5.6 first, or just skip it since 5.7 has been out 2.5 years?

For what it is worth, here is the updated query, I know it can be more optimized still, but it was what I came up as a first draft - which runs about 20X faster.

It is on our roadmap to upgrade our offices to MySQL 5.6 or 5.7 although it could require a significant amount of work because we will have to upgrade the databases of our existing customers to work with the newer version of MySQL.

You are welcome to upgrade your own office to MySQL 8.0 although we will not support that version immediately. If you do, I would be interested in hearing your experience, especially if you hit any snags.