RE: [PEAR-DEV] Idea for pear sequence tables

> From: Stefan Neufeind [mailto:stefan@neufeind.net] > Sent: Wednesday, September 24, 2003 9:37 AM > On 24 Sep 2003 at 9:13, Demian Turner wrote: > > > Apologies for the slow reply to this thread, just finished a very busy > > contract. > > Regarding putting all sequences in a single table, I'm glad this is > > being > > discussed and that the concept has been accepted in principal, when > > you have 30+ tables in a DB the additional sequences are a real mess > > :-) > > > > Browsing through Drupal code a few weeks ago I came up with a simple > > solution that may help progress towards a patch. They use a > > procedural function for the mysql driver that: > > > > - takes care of table locking > > - using REPLACE creates a new sequence when one does not exist > > But as far as I remember several people have pointed out that locking > the whole table (which stores multiple sequences) is not an ideal > solution as it affects speed of all processes which need to access > the sequence-table. On a high-load-DBMS this might lead to > performance-impacts where the one-sequence-one-table-solution would > work better. > > Hmm - we could solve this maybe with serverside functions ... > unfortunately mysql 3.x (still widely used) doesn't support this :-)) > > So what way should be chosen? Could anyone maybe give an educated > guess on the speedloss by locking the whole table? I don't see the issue. If you don't want multiple sequence tables you can use a single sequence for everything. However I don't see a point in modifying the behaviour for people that don't care. But Demian's approach gives you a choice. Which is good. BTW: something that might make sense to change is to not use mysql_last_id() because that prevent you from using BIGINT fields effectively and instead query for the LAST_INSERT_ID(). Regards, Lukas