maria-developers team mailing list archive

[dan_meany <dan_meany@xxxxxxxxx>]

Yes, flexible indexing for the dynamic columns will be key for performance, for example, for date or numeric range scans, and perhaps multipart keys. The single-table self-join method I'm currently using is indexed both id/fieldname/value and value/fieldname/id to help make searching and logical joining betwen dynamic tables perform, with additional indexes using the numeric and date equivalents for the string values where applicable. I agree there are optimal use cases for both schemaless and schemafull, and I use a hybrid model with both.     



On Aug 7, 2010, at 8:27 AM, Michael Widenius <monty@xxxxxxxxxxxx> wrote:


Hi!

"Arjen" == Arjen Lentz <arjen@xxxxxxxxxxxxx> writes:

Arjen> Hi Dan
Arjen> On 05/08/2010, at 12:28 AM, Dan Meany wrote:
Hi, I was wondering if anyone here had any opinions about this  
proposed fix for the 64 table join limit (originally by Sergei)

http://lists.mysql.com/internals/38013

With the increased use of schemaless and hybrid, partly schemaless  
application designs, this would be a big enhancement.  In my own  
application, being able to make minor, typed, virtual column  
additions for different SAAS clients in virtual tables at runtime  
without changing the physical schema or regenerating application  
binding layers is pretty priceless.  But currently we are limited to  
64 virtual columns by this mySQL join limit.

<cut>

Arjen>   - if you really want to be schemaless, don't use an RDBMS. RDBMS are  
Arjen> structured, for a reason. It helps in a multitude of ways. Sometimes  
Arjen> for whatever reason you can decide that structure is not what you  
Arjen> need, but then using an RDBMS (apart from the familiar convenient  
Arjen> interface) makes no sense.

We have plans to add dynamic columns into MariaDB soon.
This will probably happen in 5.3, assuming there is some interest in
this feature.

With dynamic columns you can store 'any' number of columns in a blob.
In effect, each row in the database may have it's own set of columns.
One will be able to trivially access and update data in the dynamic
columns and also add/drop columns inside the blob.

Example usage:

SELECT column_get(blob, 1, varchar(100)) from table_with_dynamic;

UPDATE table_with_dynamic SET blob=column_add(blob, 2, "hello") where id=1;

UPDATE table_with_dynamic SET blob=column_del(blob,4) where id=5;

Note that 'column_add()' will replace any old value with the given column_id.

Future ideas:
- Allow indexing with name instead of numbers. When this is done we can drop the
type as part of column_get()
- Allow indexing on dynamic fields.

You can find a full specification of this feature here:
http://askmonty.org/worklog/Server-BackLog/?tid=34

Regards,
Monty

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