Re: Slow running MySQL query :

[Rick James]

The default collation for the character set utf8 is utf8_general_ci, which is different than utf8_bin.

I expect the "1098762" in the EXPLAIN changed to "1"?

> We cannot change the query, as this is part of an upgrade package from Third party vendor

Complain to them, even if you get it solved. They (3rd party vendors) need to be put on notice that they do a poor job of performance.

The snippet from the Slowlog would have shown some interesting numbers, if the query had ever finished.

Check the rest of the FOREIGN KEY fields for mismatched collation!

General comments on the schema:
* NULL is often 'wrong'; consider using NOT NULL
* VARCHAR(255) is sloppy; using reasonable limits can improve performance in some situations.
* lower_... is not necessary if you use utf8_general_ci (or utf8_unicode_ci) instead of utf8_bin. That is, those fields could be eliminated, and you could simply use the non-lower fields.
* Is BIGINT needed? INT UNSIGNED allows up to 4 billion values, but with half the space required.