Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Execution of a query on a view is up to 3 times slower in comparison to the underlying query even if there's no data within underlying tables.
Root cause is function check_duplicate_names() in sql/sql_view.cc which tests for duplicate column-names within views or derived queries.
It is invoked in selects accessing a view though this check has already been made at view-creation time.
However the implementation in check_duplicate_names() can be improved to gain better performance: mariadbViewPerformance.patch
This patch has been tested in 10.1.45 but should be applicable to subsequent releases as well.
Testing of performance improvement can be done with attached SQL file test.sql and mysqlslap:
mysql -h 127.0.0.1 -u root test < test.sql
|
mysqlslap -h 127.0.0.1 -u root --create-schema=test -c 100 -q "SELECT * FROM views_perf;"
|