Indexing performance problem on MySQL 8

[romainruaud]

Since this commit : 8018fce

And the integration of the patch provided by @Wohlie we identified an issue on a client running MySQL 8.

The indexing process became suddenly very slow (from 2h to 20h) after upgrading Elasticsuite.

It seems that the query generated by the patch is problematic on MySQL 8.

Here is the explain on the client server :

For unknown reasons, it seems that it will use the index on "STORE_ID".

Here is the explain on another client server running MariaDB :

Here it uses the index on ATTRIBUTE_ID.

[robinero]

While we are not experiencing the extremely slow indexation mentioned here, we are experiencing some reduced indexing performance caused by 8018fce.

As a workaround we have applied a patch that reverts the code added in this commit.
I was wondering if there is some new information available, so that we do not have to patch to the module.

[romainruaud]

Hi @robinero

no, actually no news, for this client we also reverted the patch. We investigated but did not find anything why MySQL 8 was not dealing properly with the join clauses.

I guess you're on MySQL 8 as well ?

Regards