i use dynamic columns without problem, it don't have a good future cause it's only a lot of function to encode/decode data, in other words, the future is the today present + some new features to easly handle data or optimize space or optimize cpu/memory use

make sure you are using a good encode system, dynamic columns is a encode system, like json, like php encode/decode, like base64 encode/decode, but with some pros/cons

when using a encode system, check if cpu and data size used is ok to your problem, for example a "1024" integer, can be saved with a 4 bytes string, or a 2 bytes integer, if you have 1.000.000 rows, you can save 2.000.000 bytes (allowing a better cpu/memory use), think about encoding a 64bits integer with string, and with a bigint column, use it with 1M rows....

the problem of encoding data, isn't only encode, the problem is how database search this encoded values 

we don't have index over functions and optimizer don't rewrites virtual column and functions (yet), that's probably something that document based databases do very well something like ~ search inside documents

---

some ideas....

1) specific index idea

we could have a dynamic columns index, instead of a btree index, something like full text indexes do...

2) general idea

if we could have a "index(  some_function(field)   )", and could optimize "some_function(field) ='xxxx'" using the index, this could solve many problems of dynamic column and encoding data at database, with more complexity we could include some index optimizations to functions and "rewrite" the queries

examples....

2.1) the optimize should be inteligent to rewrite virtual columns using the same function

we have a virtual_column_indexed = substring(column,1,20), let's execute:

WHERE substring(column,1,20)='1234',  <- full table scan + execute a function each row (is substring cpu/memory intensive?)

optimizer should rewrite to 

WHERE virtual_column_indexed = '1234'  <- using index without executing the substring function each row (less cpu/memory used)

or if cpu time expent with substring function is very high and we have a virtual_column_not_index=substring(column,1,20), optimizer could rewrite to

WHERE virtual_column_not_indexed = '1234'  <- ful table scan - without executing SUBSTRING function each row

2.2) the functions should be indexed (only deterministic functions)

example

when we create a index over a function, we don`t need a new column, think about myisam storage... we don't need data at MYD file, we only need data at MYI file

index functions ~= "indexed hidden virtual columns"

when we execute 

WHERE substring(column,1,20)='1234' <- full table scan + cpu/memory use of substring function for each row

optimizer should rewrite to 

WHERE internal_hidden_column = '1234' <- internal index

2.3) the functions could explain about others rewrite tips

example...

when we execute

WHERE substring(any_column,1,20)='1234'

we can rewrite it with some understand of substring function (value,start,end)

we know that it return the same string, but starting at start position, and with a possible length of end-start, in other words, if we want to 'optimize' (or not) this function, we could rewrite to

WHERE any_column LIKE '1234%' AND   <-- (1) possible a column using index?

length(any_column)<=20 AND <-- (2) length is 'faster' than compare strings

substring(any_column,1,20)='1234' <-- (3) include the function to execute the last check if (1) and (2) are true

check if we have many negative cases to (3), we can optimize some searchs with (1) and (2)

if any_column is a index column, we have a optimization at LIKE operator (really fast)

but if we have many positive cases to (1,2) we just include more cpu time to execute two functions, in this case only substring could work faster (cause it use less cpu than 3 functions)

the same for 

WHERE substring(any_column,5,20)='1234'

could be rewrite to

WHERE any_column LIKE '_____1234%' AND length(any_column)<=20 AND substring(any_column,5,20)='1234'

2.4)if we have a very cpu intensive function, the SELECT part could be optimized too

SELECT EXPENSIVE_FUNCTION()

FROM table

WHERE single where

today we can optimize it with virtual columns...

virtaul column = EXPENSIVE_FUNCTION()

and instead of SELECT ENPENSIVE_FUNCTION we write SELECT virtual_column

but since optimizer is inteligent, why it can't rewrite the query it self?

----

check that's not a limit of dynamic columns, only a limit of optimizer/index and rewrite function to search faster when using functions

i don't know if we could include others ideas to tune/optimize database

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp