Actually if you have a unique key on 'id' selecting max(id) is extremely
fast. mysql uses the unique key to find the answer.
I had a table with 137 million records each record ~ 156 bytes (including
size of keys).
there was a unique key on sess_id and took about 0.05 seconds to select the
max(sess_id).
well anyway, sorry I couldn't help more.
thanks,
-- Andrew
----- Original Message -----
From: "Leos Bitto" <[EMAIL PROTECTED]>
To: "Andrew Schmidt" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, August 16, 2001 12:50 PM
Subject: Re: auto_increment vs. heap tables
> Uh oh... Please NO. I want the standard _FAST_ auto_increment, not this.
> "select max(id) from table x" is terribly inefficient. And the table
> is locked during this operation... That's just not acceptable for me.
> Anyway, thanks for the response. It was the second one.
>
> Leos
>
> On Thu, 16 Aug 2001, Andrew Schmidt wrote:
>
> > auto increment is really just a nice way of doing the following (really
nice
> > way actually):
> >
> > lock table x
> > select max(id) + 1 from table x
> > insert into table x values (newid)
> > unlock table x
> >
> > this is under the assumption table x as 1 field called id which has a
> > primary key on it. and newid is the id recieved from the select.
> >
> > so doing it manually with heap tables seems quite feasable just a little
> > extra work.
> >
> > thanks,
> >
> > -- Andrew
> >
> > ----- Original Message -----
> > From: "Leos Bitto" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Tuesday, August 14, 2001 6:05 AM
> > Subject: auto_increment vs. heap tables
> >
> >
> > > Hello,
> > >
> > > I am currently changing a simple PHP4-based application from
PostgreSQL
> > > 7.1 to MySQL 3.23. I have some tables in the database which I would
like
> > > to create as TYPE=HEAP in MySQL. Because I have read the MySQL manual
> > > (applause here, please!) I know that auto_increment cannot be used
there.
> > > This hurts me a lot, and given that MySQL doesn't support sequences,
it
> > > nearly prohibits the usage of heap tables in my application.
> > >
> > > However, I am still not giving up, so here's the question: how
difficult
> > > would it be to get rid of that restriction for auto_increment in heap
> > > tables? The ideal answer I am hoping for is 'yeah, that's easy - wait
for
> > > 3.23.42' or 'try this patch and let us know if it works for you'.
> > >
> > > Thanks in advance for any response.
> > >
> > >
> > > Leos Bitto
> > >
> > >
> > >
> > > ---------------------------------------------------------------------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> > >
> > >
> >
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
