[Maria-developers] 64 table join limit
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.
Hi Dan 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.
While I'm not opposed to the limit being raised if this is possible within a reasonable development effort, I tend to find that a need for this typically arises from: - query generation - views And it comes down to query patterns requiring a lot of joins or self- joins to construct something in defiance of the schema, or in defiance of the relational model. - if it's the schema, then the schema should be modified to optimally work with these queries, rather than be focused on the data that's in them. basic guideline. - if you really want to be schemaless, don't use an RDBMS. RDBMS are structured, for a reason. It helps in a multitude of ways. Sometimes for whatever reason you can decide that structure is not what you need, but then using an RDBMS (apart from the familiar convenient interface) makes no sense. - if you're battling the relational model but do want its structure rules, sometimes the OQGRAPH engine can be of help since it allows you to deal with hierarchical and network/graph type structures in a relational way. This cuts out a lot of joins and query complexity. MariaDB 5.2 has the OQGRAPH engine integrated. If you'd like assistance with the schema, or utilising OQGRAPH, we'd be happy to help. Regards, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
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
Hi Monty, all On 07/08/2010, at 11:27 PM, Michael Widenius wrote:
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
The idea is good, based on what Google's BigTable (see the Hypertable specs) introduced. It provides a mild level of denormalisation, enabling better scalability. And in the case of MariaDB, you keep the same familiar and convenient API. That's important. I think the word "column" is very wrong in the above example. It's more like "subattributes". See what the terminology in bigtable/hypertable was... Perhaps Mark has insights on this too. I'm for it, it solves a real need that people are already trying to solve (with desperately bad design decisions ;-). Regards, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
One thing about dynamic columns is that they have sped up our development cycle by a factor of 5, since we are not spending time on hard coded binding layers, and analysts can create application tables instead of developers and DBAs. We have other mechanisms in place to support typing, validation, relationships, and UI crud screens with master-detail, all dynamically, with no app restart or sql scripts. But to make it work we need really good indexing which is what we can do using a regular mysql table. The concern with the blob approach is we'd need to wait for indexing, or use an external index. I looked at couch db but it doesn't seem to have the level of transactional support we need, and mySQL does. If someone were to submit a patch for the 64 limit would that be acceptable? Dan --- On Sun, 8/8/10, Arjen Lentz <arjen@openquery.com> wrote:
From: Arjen Lentz <arjen@openquery.com> Subject: Re: [Maria-developers] 64 table join limit To: monty@askmonty.org Cc: "maria-developers" <maria-developers@lists.launchpad.net> Date: Sunday, August 8, 2010, 8:33 PM Hi Monty, all
On 07/08/2010, at 11:27 PM, Michael Widenius wrote:
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
The idea is good, based on what Google's BigTable (see the Hypertable specs) introduced. It provides a mild level of denormalisation, enabling better scalability. And in the case of MariaDB, you keep the same familiar and convenient API. That's important.
I think the word "column" is very wrong in the above example. It's more like "subattributes". See what the terminology in bigtable/hypertable was... Perhaps Mark has insights on this too.
I'm for it, it solves a real need that people are already trying to solve (with desperately bad design decisions ;-).
Regards, Arjen. --Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget.
Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
_______________________________________________ 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
Dan Meany wrote:
One thing about dynamic columns is that they have sped up our development cycle by a factor of 5, since we are not spending time on hard coded binding layers, and analysts can create application tables instead of developers and DBAs. We have other mechanisms in place to support typing, validation, relationships, and UI crud screens with master-detail, all dynamically, with no app restart or sql scripts. But to make it work we need really good indexing which is what we can do using a regular mysql table. The concern with the blob approach is we'd need to wait for indexing, or use an external index. I looked at couch db but it doesn't seem to have the level of transactional support we need, and mySQL does.
If someone were to submit a patch for the 64 limit would that be acceptable?
I think it would be useful unless you force us always to use this limit. In other words the maximum number of tables in the query should be a parameter of the server. Regards, Igor.
Dan --- On Sun, 8/8/10, Arjen Lentz <arjen@openquery.com> wrote:
From: Arjen Lentz <arjen@openquery.com> Subject: Re: [Maria-developers] 64 table join limit To: monty@askmonty.org Cc: "maria-developers" <maria-developers@lists.launchpad.net> Date: Sunday, August 8, 2010, 8:33 PM Hi Monty, all
On 07/08/2010, at 11:27 PM, Michael Widenius wrote:
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
The idea is good, based on what Google's BigTable (see the Hypertable specs) introduced. It provides a mild level of denormalisation, enabling better scalability. And in the case of MariaDB, you keep the same familiar and convenient API. That's important.
I think the word "column" is very wrong in the above example. It's more like "subattributes". See what the terminology in bigtable/hypertable was... Perhaps Mark has insights on this too.
I'm for it, it solves a real need that people are already trying to solve (with desperately bad design decisions ;-).
Regards, Arjen. --Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget.
Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
_______________________________________________ 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
_______________________________________________ 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
Without hijacking the thread too much, Monty mentioned at OSCON about support for name:value pairs in a row. Postgres recently added a similar feature. Perhaps they can respond to that in addition to your question. On Wed, Aug 4, 2010 at 7:28 AM, Dan Meany <dan_meany@yahoo.com> 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.
_______________________________________________ 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
-- Mark Callaghan mdcallag@gmail.com
participants (5)
-
Arjen Lentz -
Dan Meany -
Igor Babaev -
MARK CALLAGHAN -
Michael Widenius