dbal: Performance degradation in MySQLSchemaManager
Bug Report
| Q | A |
|---|---|
| Version | 3.4.1 |
Summary
The changes from #5580 unfortunately severely degrade the performance for schema lookups with MySQL/MariaDB. The culprit is the newly added JOIN to filter out views in selectTableColumns(). On my test setup the query took over 50x longer (~480ms compared to ~8ms) which results in quite a big hit in some applications.
This seems like an odd DB performance problem. Interestingly, the following query (for the foo table) still is fast:
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'foo'
So the workaround could be to replace the JOIN with a subquery:
[…]
WHERE TABLE_SCHEMA = 'foo' AND TABLE_NAME IN (
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'foo'
)
At least in my tests this solved the issue.
/cc @morozov
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 19 (10 by maintainers)
Looking closer at https://github.com/doctrine/dbal/issues/5195, I recall that the problem was caused specifically by the fact that we used the
TABLE_SCHEMAcolumn of the main table in the JOIN condition (e.g.ON t.TABLE_SCHEMA = c.TABLE_SCHEMA). Prior to this bug being fixed, it resulted in MySQL prior to 8 and the corresponding versions of MariaDB scanning all databases on disk using filesystem operations instead of using a more optimized internal schema.Could you check if replacing the right part in the
ONclause of the JOIN condition with a literal solves the problem? Additionally, you can try running EXPLAIN on different versions of the query and see if there is a difference.