Possible database optimisations

[nitriques]

Affected Symphony version(s) : 3.0.0
PHP version(s) : all
MySQL version(s): 5.7+
OS(es) : all

There are 3 possible optimisation that can be done on the current 3.0.x codebase.

1. Convert long or = statements on the same column into in query.
2. Convert long and != statements on the same column into a not in query.
3. Optimise sorts using sub-queries, Re Placeholder #657 but I have not found any reasons why to use that since we now run on InnoBD.

Yes/No/Maybe ?

[michael-e]

Numbers 1 and 2 sound very good to me. I often experienced MySQL being slow especially with OR queries.

Number 3 is unclear to me, are you sure that you linked the correct issue? And how does InnoDB influence this consideration? Is it any better with complicated sorts?

[nitriques]

@michael-e Thanks!

3. See the commit 6a18bba

And how does InnoDB influence this consideration?

Since it is a new engine, performance can differ. The test made at the time of the commit may longer be true.

Is it any better with complicated sorts?

I've read most of https://dev.mysql.com/doc/refman/5.6/en/statement-optimization.html and they almost never use sub-queries for order by. But again, we would need to benchmark it.

The good news is, we can now, write integration tests to be able to track how our optimisations performs. I would use this framework do to it https://github.com/phpbench/phpbench