i was trying to work around MDEV-4414 with virtual column + index
but, check my problem... here the table definition:
now the problem... i have about 50k rows in this table..
result => using index condition, key = 'tmp_substr_key', 1 rows
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 =] )