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@askmonty.org> wrote: Hi! "Arjen" == Arjen Lentz <arjen@openquery.com> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp