maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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@xxxxxxxxxx>:

> @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@xxxxxxxxxxxxx>
> wrote:
>
>> i think a good start is "expression index​es" 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@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>


-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle