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.

Define Query Groups then add syntax like:


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:


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. 

"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.