Hi Peter :) i will talk about what i used and what i consider interesting to have, 2015-01-28 19:25 GMT-02:00 Peter Laursen <peter_laursen@webyog.com>:
@Roberto. A comment to "i think it's a nice human readable format for arrays/objects, nothing less nothing more".
But why will you then need to **store** as JSON?
about "store": 1) it's nice to read data without any tool (function), it's easy to read a object or a array, maybe could use YAML or any other format (i don't like XML cause it consume too many space) 2) saving json could optimize cpu use at php/application server without decode data every read (if you use json a lot, not my case) 3) could be nice to export/import data? i don't know, today i use table dump when i need, or a small script or pre defined data format about "json internally implemented at database / store": 1)seach with index, jpath/xpath or others functions, consume less space when possible json/xml specific field type 2)today when i use json i save using a blob column and execute a compress() function when possible, sometimes i don't care about datasize, and only save at blob columns (or maybe varchar when data is small) about "using json as part of sql language" 1) could be nice use field->something or {asdf:'abc','dfs':'asd'}, to create/handle dynamic columns instead of using functions Could not something like "SELECT .. INTO JSON ..." do the trick?
yeap, that's not a problem, today i execute json encode/decode at client side only when i need (<10% of all selects/insert/update) i prefer each column with one value, i don't need dynamic columns every time and i can create more columns with spider vp/online alter table when i really need maybe this (INTO JSON) could be nice if we have a http interface to mysql?! or maybe it's nice to someone that use ajax everytime, not my case
(with hindsight: "SELECT .. INTO XML ..." could have been implemented in MySQL when other XML functionalities were implemented but never was, it seems. You can mysqldump to XML but not "SELECT .. INTO XML ..." - using OUTFILE or not - refer http://dev.mysql.com/doc/refman/5.6/en/select-into.html)
after heidisql allowing export data to html and others formats, i stoped use of INTO part of SELECT, that wasn't usefull to me i think it's not usefull using INTO JSON too, maybe if we could import it to excell or other tool that really use it, but i think it's only to ajax applications, or some export method (convert from one database to another?), maybe a connect table with json could be more interesting? i don't know, today i only see that i could optimize some queires using dynamic columns if i could index it, that's my today big problem with cpu use and disk use, not all rows i have the column, in this cases it could save as NULL, that's my today big problem i see dynamic columns, json, yaml, xml as encoding methods with some functions,without options to optimize the use of encoded data and optimize the store, for example, we could optimize dynamic columsn if we have a new field type? we could optimize yaml if we have a new field type? today i'm using blob + compress() when i need
-- Peter
On Wed, Jan 28, 2015 at 7:29 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i think a good start is "expression indexes" and after json syntax to SQL language, and some new functions to handle json/etc
CREATE INDEX name ON table ( some_function(field) ) maybe dynamic column functions, or json functions? or any function deterministic (for example, NOW() function can't be used)
and use it at WHERE clausules
WHERE some_function(field) ='1234' <- use index WHERE some_function(table.field) ='1234' WHERE some_function(field) IN ( sub select ) WHERE some_function(field) LIKE "blabla%" WHERE some_function(field) LIKE "%blabla%" WHERE some_function(field) LIKE "%blabla"
_______________________________________________ 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
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle