Thank you for putting time into the question (seriously).
It might be a better fit for codereview.stackexchange.com, as you need a large amount of code reviewed and it's not broken; you are just looking to improve it. It also looks like it could take a substantial amount of time to setup a test environment to give an accurate answer.
I think it would be fine for SO if you could narrow the problem down to a single problem/question, for example:
- "I profiled the query and noticed that the outer join between ABC and XYZ always causes an index scan. How can I optimize it to achieve an index seek instead?"
- "I tried to abstract this logic into a function to simplify the query and encountered ABC."
Right now it's very localized; someone else would have trouble gaining much benefit from it.
If the question was more like example #1, then a user looking to troubleshoot a slow outer join on indexed columns might find it useful. If it was structured like example #2, it might contain your specific code, but it would still be a useful reference for incorporating/troubleshooting a function in a MySQL query.
I'm a developer with a fair amount of database experience, so I don't think twice when I see a database-related question. But being objective, your question (or perhaps a more focused version) might be better suited to dba.stackexchange.com. That may be the source of the "off-topic" verdict.