Details
-
Type:
Task
-
Status: Closed
-
Priority:
Minor
-
Resolution: Duplicate
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Hi guys,
i was trying to work around MDEV-4414 with virtual column + index
but, check my problem... here the table definition:
create table t ( linha_digitavel varchar(54) not null default '', tmp_substr char(37) as (SUBSTRING(linha_digitavel,1,37)) persistent, key linha_key (linha_digitavel), key tmp_substr_key (tmp_substr) );
now the problem... i have about 50k rows in this table..
explain select * from t where tmp_substr='abc'
result => using index condition, key = 'tmp_substr_key', 1 rows
explain select * from t where SUBSTRING(linha_digitavel,1,37)='abc'
result => using where, full table scan
—
well i see two kinds of optimization here...
1)the MDEV-4414, where substring(?,1,?) could use index "key linha_key (linha_digitavel)"
2)the "SUBSTRING(linha_digitavel,1,37)" could be rewrite as "tmp_substr"
(2) is the MDEV feature requested here
it give DBA an oportunity to optimize closed systems, that we can't change source code, just tables...
think about a sloowww query that could be optimized with a virtual column + index + sql rewrite ! ok many job... but it's a nice feature
think about determinist functions (md5 for example) that could be optimized just replacing the "determinist_function(some_parameters)" to the virtual column, and the option to index it and make it really faster!
think about MongoDB like features... a schema less table with index on fields that may or may not exists based on LONGBLOB fields + Dynamic Column + Virtual Index, i could say goodbye mongoDB for small systems (ok the map reduce is a parallel operation with high query performace, but it's not mysql =] )
Gliffy Diagrams
Attachments
Issue Links
- is blocked by
-
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
