[drupal-devel] LOCK vs. SELECT FOR UPDATE

[Theodore Serbinski]

On 9/28/05, Dries Buytaert <dries.buytaert at 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)