On 9/28/05, Dries Buytaert <dries.buytaert@gmail.com> wrote:
On 27 Sep 2005, at 18:18, Theodore Serbinski wrote:
Drupal could certainly utillize this to it's advantage with better optimized queries and less database overhead functions.
Mind to be a bit more specific?
Well first off, foreign key support. I know this was introduced in one of the latter 3 versions, 3.23.44, to be exact, but it wasn't until version 4 that most of the foreign key constraints were properly working and all of the bugs squashed. I'd love to see Drupal move to an InnoDB table storage adding foreign key constraints. This would add a tremendous amount of data integrity to Drupal and we could eliminate some overhead. For example, there was just a thread about a user being deleted and then all of the forum posts being deleted as well. If we had things setup with foreign keys, we could something like "ON DELETE SET NULL" in which case all of these posts would be associated with a null user... we could even maybe associate these posts with UID=1 but that is another issue to discuss. As a result of this ON DELETE, ON UPDATE mechanisms, we could delete any other queries in Drupal that clean out other tables as certain rows are delete. Not to mention if a user in phpMyAdmin tries to delete a table, foreign keys can prevent many (but not all) of these tables from being deleted because of foreign key constraints. This can also help future modifications from accidently breaking the database in Drupal as well. More info: http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html MyISAM is really best *only* for INSERTs and SELECTs if you read carefully above. Sure there are some tradeoffs but I would like to see this. Additionally, if we move to 4.1 (which is the recommend release now but might be a bit too far ahead now) we could make use of subselects. Once instance came up with the new upload admin consolidation ( http://drupal.org/node/25756 which I wonder why no reviews yet? Patch has been in since the freeze a while back). To perform some of the aggregate queries, subselects would have been better instead of running a single query, that looping through in PHP to get the aggregate results. Now the initial benefits of moving to supporting 4.0 are minimal, however, I think we could massage Drupal a bit into better using some of these advanced features and I'm sure we could cut some bloat and simplify things elsewhere. I haven't looked into this too detailed yet, but I would like to keep this on the horizon. ted (aka m3avrck)