Re: [Maria-developers] Help with MDEV-4419, ENUM optimization
hummm :) nice, i will study it =) it give me a lot of speed, since some system are closed source and i can't rewrite the query, just change the mysql server or put a mysql proxy
i will put this info in MDEV, maybe some one will try do this before me and will ask again
should i use SQL_SELECT::test_quick_select() ? or just get_quick_select_for_ref() ? i didn't understand if test_quick_select is just for keys (index) or any WHERE condition 2013/6/11 Roberto Spadim <roberto@spadim.com.br>
i will put this info in MDEV, maybe some one will try do this before me and will ask again
-- Roberto Spadim SPAEmpresarial
i was reading file "opt_range.cc", but i'm a bit confused (maybe first impression) where the per column check occurs? where the "Impossible WHERE" condiction occurs? -------------------------------------- i'm thinking about a implementation like this (per column): field = ENUM field WITH enum_values[i], where i start with 0 to ENUM VALUES + 1, example: field ENUM ( 1,2,3,4,5,6 ) NOT NULL max i = 6 field ENUM ( 1,2,3,4,5,6 ) max i = 7 enum_value[0]=1, enum_value[1]=2, enum_value[2]=3, enum_value[3]=4, enum_value[4]=5, enum_value[5]=6 enum_value[6]='' -> this allow insert of 10000 value in this field enum_value[7]=NULL -> if ENUM accept NULL add +1 ------------------------------------- OP could be: =,!=,<>,<,<=,>,>= (field OP constant_value) REWRITE TO ( (field OP constant_value) AND ( enum_value[0] OP constant_value OR enum_value[1] OP constant_value OR ... enum_value[max i - 1] OP constant_value OR enum_value[max i] OP constant_value ) ) ------------------------------------- (OP BETWEEN a AND b) REWRITE TO ( (field BETWEEN constant_value_1 AND constant_value_2) AND ( (enum_value[0] BETWEEN constant_value_1 AND constant_value_2) OR (enum_value[1] BETWEEN constant_value_1 AND constant_value_2) OR ... (enum_value[ max i - 1 ] BETWEEN constant_value_1 AND constant_value_2) OR enum_value[ max i ] OP constant_value ) ) -------------------------------------
hi guys, i was trying to understand get_mm_leaf of opt_range.cc debuging with VS 2008 but, i think that "get_mm_leaf" function, only execute when we are on a 'indexed field' anyone know if there's a rewriter before get_mm_leaf and others index optimization? maybe it do something that change: a>10 and a< 12 to a=11 i will use it to do enum rewrite in WHERE part, like: enum('a','b','c') not null enum='d' => will be rewrite to 0 (impossible where) enum!='d' => will be rewrite to 1 (always true) enum <'b' => will be rewrite to enum in (0,1) enum >'c' => will be rewrite to 0 (impossible where) and others inequalities operators "LIKE", !=, <, <=, >, >= thanks
hum, should i use this function in sql_select.cc? remove_eq_conds() /** Remove const and eq items. Return new item, or NULL if no condition cond_value is set to according: COND_OK query is possible (field = constant) COND_TRUE always true ( 1 = 1 ) COND_FALSE always false ( 1 = 2 ) SYNPOSIS remove_eq_conds() thd THD environment cond the condition to handle cond_value the resulting value of the condition NOTES calls the inner_remove_eq_conds to check all the tree reqursively RETURN *COND with the simplified condition */
participants (1)
-
Roberto Spadim