[MDEV-31125] Add option for default local schema when I_S is queried Created: 2023-04-25 Updated: 2023-12-15 |
|
| Status: | In Review |
| Project: | MariaDB Server |
| Component/s: | None |
| Fix Version/s: | 11.2 |
| Type: | Task | Priority: | Major |
| Reporter: | Andrew Hutchings | Assignee: | Sergei Golubchik |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Issue Links: |
|
||||||||
| Description |
|
If you have many thousands of databases, each with tables and an application you have no control over runs a query on I_S.COLUMNS or TABLES this can cause a significant amount of disk access which does not perform well. In many cases all that is really needed is the output for the current default schema. This is particularly true for hosting environments where it is typically a schema per application and you do not have control over the queries being executed by the application. The proposed solution is a new server variable with both a global and local option. When enabled I_S.COLUMNS and TABLES will only scan the current default schema if an explicit schema has not been provided in the WHERE condition. |
| Comments |
| Comment by Andrew Hutchings [ 2023-04-26 ] |
|
Proof of concept commit: https://github.com/LinuxJedi/server/commit/2b08d2bbac79bacf1aa32fabfbb3f2f98d995e3d |
| Comment by Andrew Hutchings [ 2023-04-27 ] |
|
Updated patch to solve an issue: https://github.com/LinuxJedi/server/commit/3529aba0174c44a740bfd50856b351c65acdda8c Need to fix the skipping of the passive filter when table_name is provided (uses_only_table_name_fields() detects either table and schema name). |
| Comment by Andrew Hutchings [ 2023-05-10 ] |
|
Solved the remaining problem. PR opened. |
| Comment by Andrew Hutchings [ 2023-05-16 ] |
|
PR reviewed by monty, minor changes made (described in a comment in the pull request) |