WL#683: New System Tables - per Query and per User session variables and Indexes optimization
Status: Assigned — Priority: Medium
With 4.0 SET SESSION syntax it is possible to tune every query with setting specific session variables. It is also possible to optimize some queries by USE/FORCE/IGNORE INDEX() Specifying such per-query tunnings adds too much to Queries text. Proposal: Define Query Groups then add syntax like: SELECT SQL_QUERY_GROUP=19 SELECT .... Where for Query_group 19 DBA can store set of SESSION values optimized for this GROUP of queries Group can even has descriptive name like "BLOB handling query" where for example max_allowed_packet is increased above GLOBAL setting Add new system table query_ qroup (Category sounds better maybe) One or 2 tables depending on what we can choose as best structure. query_group (id. session_variable, value, Primary key(id, session_variable)); Second part is more diffcult to define so I wll only outline the idea: Instruct Optimizer that upon: SELECT SQL_QUERY_TYPE=10 SELECT .... QType=10 implies INGNORE INDEX (1) for First table, FORCE INDEX (3) for Second table and so on.... I believe overhead will be minimal because these tables will be checked only when Query Type is explicitly defined within query. Note also that this gives DBA some ability to prioritize Queries by Groups. Like: "WebQueries" - all buffers set to minimum "Maintenance qeries (backups, statistics, etc)" - biggest possible buffers "Slow connection Queries" - queries with Increased Timeouts and so on... Same approach can be used for USER Groups - where every user group is assciated with Set of Session variables User can change them ofcourse, but these will serve as Default values. Now it can be achieved by .my.cnf files, but they are user specific so it is not trivial to change same value for all users in group at once. I hope all of the above will be well appreciated by users.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.