Hi Brian
On 05/02/2009, at 8:22 AM, Brian Aker wrote:
Auto-increments have always had a set behavior... some of this
worked well, and some of it causes bottlenecks.
1) They always increase in value in the table. You can use them as a
"what came before what".
2) Gaps don't (for the most part) exist).
What are people's feelings about the above assumptions? Is it ok to
break them, or would it be better to add a different type which did
not have the above assumptions, and leave the old ones in?
Open Query teaches:
- later inserts usually get a higher number, but don't bet on it
(with NDB and multi-master, it doesn't).
- expect gaps (rollbacks, NDB and multi-master again).
- the only expectation you may have is that it gets a # unique
within the specified table. No more.
This story is actually quite similar to the "rows have no order"
teaching. They may appear to sometimes come out in some kind of
predictable order (order of insert, or index order) but you can't bet
on it and fact there's no reason to. We generally do an exercise that
makes the presumptions fail and people remember.
Anyway, both are about good habits, there's no need to indulge any bad
habits here. For broken app code, let it still be broken.
I figure that whatever Drizzle comes up with will still have potential
gaps and/or out-of-sequence inserts, and IMHO that's perfectly fine;
trying to avoid them involves serialisation which will cause
contention somewhere. Not worth it, not even to appease beancounters.
I agree with Jim that a truncate should not reset the ID generator.
It's a side-effect of the implementation in MyISAM, and it can easily
be avoided even without slowing down anything. So if any other engines
do the same, make 'em not do that.
On a practical note, the naming of both "auto-increment" and
"sequence" is unfortunate, because it causes implicit expectations on
the behaviour. UNIQUE_ID or GENERATOR (thank Jim!) would be clearer,
it's just intended to provide a unique ID, and nothing else.
And while in principle I can see how having it user-definable in terms
of logic can be useful, I don't think it's essential.
Cheers,
Arjen.
--
Arjen Lentz, Director @ Open Query (http://openquery.com.au)
MySQL Training from $475/day, DBA/Support from $249/month
My blog is at http://arjen-lentz.livejournal.com
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
