maria-discuss team mailing list archive

[Tom Worster <fsb@xxxxxxxxxx>]

Hi Daniel,

Yes, I shall add remarks about indexes to the docs.

I didn't have any purpose for "token" other than I needed a name for the
strings that go in the SQL that refer to dynamic columns. Is there a problem
with that particular word or do you think it would be better to do without
any such name?

You're spot on about computed columns. From my point of view, doing that
amounts to adding the column to the schema.  The column name appears in DDL
and the corresponding attribute is therefore no longer ad hoc in the the
application. The same point applies (in some imaginary future) to adding an
index to a dynamic column without needing a computed column. If attributes
represented in the application are to be truly fluid then their
corresponding dynamic column cannot be indexed, at least not as I understand
indexes in MySQL.

Thanks for taking a look.

Tom


From:  Daniel Black <daniel.black@xxxxxxxxxxxxxxxx>
Date:  Saturday, May 23, 2015 at 1:27 AM
To:  Tom Worster <fsb@xxxxxxxxxx>, maria-discuss email list
<maria-discuss@xxxxxxxxxxxxxxxxxxx>
Subject:  Re: [Maria-discuss] Dynamic Columns ORM


They are generally good for blob like information that is generally
retrieved in a query with some structure around name->value pairs.

You mentioned (forum) that there isn't an index on these items so you'll get
more row scanning on these if people search on a Dynamic attribute tokens
(not sure why you are adding "token" to the language here). This limitation
to be mentioned more in the documentation you've written.

You could use computed columns to extract a variable to index if really
needed but you'll need to ask why isn't it a column in its own right.
https://mariadb.com/kb/en/mariadb/virtual-computed-columns/

Enhancing frameworks to expose database features is a good thing as they
often get omitted in the reduction to the lowest common factor (mysql-5.0?)
and sometimes the lowest common factor across db implementations (ansi
SQL(?)).

-- 
-- 
Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.