Any SQL experts here?

Mark Goodge wrote:
> On Tue, 05 Feb 2008 13:01:46 -0500, Jerry Stuckle put finger to
> keyboard and typed:
>
>> Auggie wrote:
>>>
>>> If you are using PHP, I do beleive they have a special function just for
>>> this purpose:
>>>
>>> mysql_insert_id()
>>> http://www.w3schools.com/php/func_mysql_insert_id.asp
>>>
>>>
>>>
>>>
>> Not reliable. What happens if two people insert at virtually the same
>> time? The first might get the value of the second.
>
> I think you'll find that the developers of MySQL have thought of that
> possibility
>
> To be more precise, MySQL implements write-locking, so it's impossible
> to have two simultaneous writes to the same table. MySQL's
> LAST_INSERT_ID() statement returns the last insert on a per-connection
> basis, so a subsequent insert by a different connection won't affect
> the value returned by the first connection. In PHP and Perl (and, as
> far as I'm aware, in pretty much any other language as well),
> connections to MySQL are persistent throughout the runtime of a script
> unless explicitly closed. So, provided you don't do any other SQL
> statements in between the insert and using whatever function you have
> available to retrieve the insert ID, the insert ID retrieved by any
> instance of a running script will always be that of the same script
> instance's previous insert. Insert ID's can't "leak" between different
> connections, no matter how many of them are running simultaneously.
>

True. But please read the post I was responding to. There is a certain
amount of time between when the first write finishes and the SELECT for
MAX(id) is performed. It is quite possible for another script to get in
the middle and add another row. It is a very unsafe way to do it.


> I've worked with PHP and Perl accessing MySQL and using
> LAST_INSERT_ID() on large (ISP level) installations and never had a
> problem with it yet.
>
> Mark

I never said there was a problem with fetching the last insert id. I
was referring to using MAX(id).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.DeleteThis@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:

> "Beauregard T. Shagnasty" wrote:
>> http://www.phpfreaks.com/quickcode/Get_the_ID_of_Last_MySQL_Entry/20.php
>
> Eeewwww....
>
> If you're going to read the last value returned by an identity column,
> do it within SQL and do it within some sort of atomic transaction.
> The idea of setting it in one line of PHP and then reading it in
> another line is just icky.

That's why they are php "freaks", eh?

> Who's to say another process hasn't incremented the thing in the
> meantime?

That caveat is mentioned at http://us.php.net/mysql_insert_id
in the benfoldsforever comment.

Or were you thinking you were replying to a poster of MAX() ?

--
-bts
-Friends don't let friends drive Vista
 >> Stay informed about: Any SQL experts here? 

SpaceGirl wrote:
> Quick SQL question:
>
> Table A contains articles
> Table B contains images that belong to articles
>
> Inserting a new record (article) into table A, which has an
> incrementing primary key. Several entries also need to be made in
> table B, containing the images that belong to the article in table A.
> How do I get the key value from A and put it in B at the same time as
> inserting A.
>
> Platform: mySQL 5.
>
> Me... stuck.


Thank you everyone for your posts, plenty for me to try in the morning.

BTW, I'm my SQL exposure doesn't go much beyond SELECT * FROM and simple
INSERT statements as I'm a multimedia designer

I only program server site stuff if I really *have* to. These days I
spend half my time writing AS3 for Flash applications, and the rest
doing PhotoShop/Video work.

I rarely get to touch server side - except for this project, which
requires me to manipulate (or create) a lot of database/ASP generated
XML for Flash to consume. Phew. Anyway, thanks again!

x


--

x theSpaceGirl (miranda)

http://www.northleithmill.com

-.-

Kammy has a new home: http://www.bitesizedjapan.com
 >> Stay informed about: Any SQL experts here? 

On Feb 5, 11:03 pm, Jerry Stuckle wrote:

> Time is not necessarily unique.

High resolution time can be unique... sometimes.
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 10:10, Dylan Parry <use... DeleteThis @dylanparry.com> wrote:

> unique AFAIK, they are based on a combination of a MAC address, the
> time and processor cycles or something, so they'll always be unique.

GUID are always unique, but there's a rate limit to how fast they can
be allocated (per MAC address). Potentially a machine with more NICs
in it could allocate them at a proportionally faster rate, but I've
never seen it done.

The _vast_ advantage of GUIDs is that they can be allocated uniquely
by a number of separate non-communicating machines. If you need them
more quickly, just have more machines doing the allocation work,
conveniently the "client" machines that are feeding the central DB
server. Allocate them outside the SP and pass them in as data
parameters, you don't have to allocate them _when_ the new row is
added to our "Table A".

Goodbye to the centralised Identity column allocation bottleneck! Now
_that's_ worth having!!


> The problem with GUIDs is that they take up a fair bit of space as they
> are quite long, which of course makes them trickier to index.

Red herring (although proving it in all cases isn't simple).

Firstly they're not really that big. 128 bits is only 2 words (4 words
on little hardware). 16 ASCII characters. You're not having to handle
them as the big long string that they look like when printed!

Secondly, column length isn't such a big deal for indexing, compared
to the number of index values and the size of the resultant indices.
Entropy per bit in GUIDs is pretty low (certainly compared to ordinal
word values) and using a longer type to address the rows themselves
just doesn't change the index performance that much.

Thirdly, just benchmark it. Not a problem.

Fouthly, Kimball recommended it and he does rather know what he's on
about.
 >> Stay informed about: Any SQL experts here? 

On 5 Feb, 23:02, Jerry Stuckle <jstuck... DeleteThis @attglobal.net> wrote:

> > If you're going to read the last value returned by an identity column,
> > do it within SQL and do it within some sort of atomic transaction. The
> > idea of setting it in one line of PHP and then reading it in another
> > line is just icky. Who's to say another process hasn't incremented the
> > thing in the meantime?
>
> Because mysql_insert_id() is guaranteed to get the id of the last
> insertion of the current connection.

That's certainly better than it being global to PHP, but it's still
not perfect.

1. It's just not _necessary_ (do it from SQL, even return it from the
SQL stored procedure afterwards if you want to print it out
afterwards). This is PHP's ghastly "swiss army knife" design principle
where any random dregs get added to the language "just in case they
come in handy", not from any sensible design principle.

2. It encourages PHP code that's tightly coupled to MySQL, not to a
generic SQL back end. This example isn't rocket science, there's no
need for it to start tying itself into platform-specific features.
Leave the DB code in the SQL and build a better, more generic, more re-
usable PHP product.

3. It's separable from the PHP call that executes the SQL query and
actually allocates the identity. In this example they're on adjacent
lines, but what happens if they start being a screenful apart? This
is how working brittle code turns into non-working broken code over
times.

4. Connection pooling. A pooled DB connection can certainly break this
scope model, if you make two allocating operations on it before
reading the identity value(s).
 >> Stay informed about: Any SQL experts here? 

On Feb 6, 12:33 pm, Jerry Stuckle wrote:

> mynameisnobodyodys... wrote:
> > On Feb 5, 11:03 pm, Jerry Stuckle wrote:
>
> >> Time is not necessarily unique.
>
> > High resolution time can be unique... sometimes.
>
> The salient point key being "sometimes".

The salient point key was meant to be high resolution
(for a non-parallel system).
Anyway, time was just a suggestion
(or a combination of things including time
plus maybe a random string),
but anything unique would obviously do.
 >> Stay informed about: Any SQL experts here? 

mynameisnobodyodyssea RemoveThis @googlemail.com wrote:

> High resolution time can be unique... sometimes.

Exactly. Sometimes If you want a unique ID, in MSSQL you can use
GUID, but no idea whether something similar exists for MySQL. Certainly,
if I wanted a unique ID I could generate a GUID in C# and then insert
the record into the database already knowing what the ID would be
without having even inserted the record, and it would most definitely be
unique AFAIK, they are based on a combination of a MAC address, the
time and processor cycles or something, so they'll always be unique.

The problem with GUIDs is that they take up a fair bit of space as they
are quite long, which of course makes them trickier to index.

--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk

The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 12:35, Jerry Stuckle <jstuck... DeleteThis @attglobal.net> wrote:

> Are GUID's always guaranteed unique?

Yes, if we assume some fairly reliable real-world assumptions about
their broad allocation.

UUIDs may be guaranteed unique, but the algorithm for those is
something of a superset of the GUID. Personally I prefer to use the
term GUID, because almost all of the allocation code used for them is
GUID generation, not UUID.

The way they achieve this in a finite space is simply by restricting
the number of them that exist. If you have a high-rate application,
this can actually become a bottleneck (but the algorithm parallels
trivially).
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 12:33, Jerry Stuckle <jstuck....TakeThisOut@attglobal.net> wrote:

> > This is PHP's ghastly "swiss army knife" design principle
> > where any random dregs get added to the language "just in case they
> > come in handy", not from any sensible design principle.
>
> No, the mysql_insert_is() is a MySQL call, not PHP.

LAST_INSERT_ID() is a MySQL function, mysql_insert_id() is PHP

It's not even a good facade for LAST_INSERT_ID(). Most grievously the
return type is wrong, so it doesn't work with bigints. That's just
shoddy.

Secondly it operates at different scope to the MySQL function, that of
the connection. It's a pathological case to construct an example where
that would break something, but it's still amateurish language design.

Thirdly, and probably the most likely to raise a real live bug, it
only scopes to a connection if you specify one by parameter, otherwise
it defaults to the last one _opened_ (not the last one with an execute
on it). So now you can break PHP your (sloppy) PHP code by changing
startup code that's pages and pages away.

Fourthly it uses that nasty PHP business of recompiling the PHP
language engine with MySQL support switched on, rather than having a
dynamically loadable (or even a statically bindable) driver component
for DBs.


> Every database nowadays has it's own extensions to SQL.

Of course. So we use the well-known principle of "encapsulation" to
_hide_ this, not make it more and more manifest thorugh higher levels
of our application language.

> The real problem here is the ANSI SQL spec is lacking,

Can't argue with that!


> > 4. Connection pooling. A pooled DB connection can certainly break this
> > scope model, if you make two allocating operations on it before
> > reading the identity value(s).
>
> Only if the pool is broken.

Perhaps. A good pool should detect this (as a scoping error) and raise
an exception, but it still won't work.


What's _wrong_ with using LAST_INSERT_ID() insert some neatly packaged
SQL module, then throwing the value away afterwards because it really
isn't necessary to know it afterwards? That's good design, according
to "modular programming" concepts that have been regarded as a basic
starting point for good design for 30-some years now.
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 11:12, Dylan Parry <use....TakeThisOut@dylanparry.com> wrote:

> TBH, I was just going on what I have been told in the past. I've not
> actually noticed a speed hit myself, but I always assumed that was
> because I had puny databases!

Small, fast things are in general small and fast.


Remember the three rules of optimisation:

* Don't.

* Don't do it yet.

* Don't do it to things that weren't actually broken, so as to need it.
 >> Stay informed about: Any SQL experts here? 

On 6 Feb, 12:50, Jerry Stuckle <jstuck....RemoveThis@attglobal.net> wrote:

> > GUID are always unique, but there's a rate limit to how fast they can
> > be allocated (per MAC address).
>
> Wikipedia disagrees with you:
> http://en.wikipedia.org/wiki/Globally_Unique_Identifier

That's because I don't have time to fix _all_ of wikipedia.


> "While each generated GUID is not guaranteed to be unique,

GUIDs are unique provided they're merely global, not universal.
Generating duplicates is forbidden in principle, but this does rely on
industry rules about MAC allocation. Ask dorayme if you want to know
how these operate off-planet.


> The only way something can be guaranteed to be unique is to have it
> perfectly unbounded - which we know is impossible.

That's one way. The other, and simpler way, is just to restrict the
number that can exist.


> The disadvantage is they require more disk space

Can't afford 128 bits? You must be paying your developers awfully
cheaply if that's a remotely good trade-off.

> and put a lot of unnecessary work on the server.

They're more efficient to allocate than sequences (especially for
isolated sequences on Oracle or DB2). The algorithm is trivial,
because it's simply generative, not collaborative. A new GUID can be
allocated without any reference to the last value stored. In most
cases (at slower rates) it doesn't even need to check the last value
generated.

It's also possible to off-load them entirely from the central server,
and to allocate them safely and reliably on the clients.


> Most identity columns are integers for
> a simple reason - they're small and can be compared quickly.

Yes, two reasons which are simply irerelvant these days. Comparing
execution speeds for compare operations on 64 bits vs. 128 bits just
isn't something I've worried about this decade. For practical purposes
on DB servers today, GUIDs are also "small" and "quick". You might
have a point for embedded systems, but we're talking about web-app DB
servers here.


> Using a non-integer column increases the amount of space required for
> each row. And since identity columns are also generally indexed, it
> adds to the size of each entry in the index(es).

> This affects performance in many ways

It only affect the size if you count bytes, not entries. It may then
affect size in pages (i.e. rows per page), but this isn't as big an
effect as you might expect. For non-primary keys (i.e. the search
operation is taking place over a non-GUID value and the GUID is merely
used for referencing), then the effect is trivial.

> Even just comparing GUID's instead of integers is a significantly longer process.

Of the order of a few clock cycles. That's just not important.


> > Goodbye to the centralised Identity column allocation bottleneck! Now
> > _that's_ worth having!!
>
> Hello to the performance bottleneck. That's NOT worth having!!

I know you haven't used or benchmarked them, so why would you think
that?

> > Secondly, column length isn't such a big deal for indexing, compared
> > to the number of index values and the size of the resultant indices.
> > Entropy per bit in GUIDs is pretty low (certainly compared to ordinal
> > word values) and using a longer type to address the rows themselves
> > just doesn't change the index performance that much.
>
> With small tables, no. With large tables, yes.

Why does the index length (in entries) affect this linear scaling
factor?

> > Thirdly, just benchmark it. Not a problem.
>
> I have - on large tables. Have you?

Read my patents.

> Try it with a 100M row table, for instance.

Why would I want to do such a pointless test? Stop worrying about
number of rows in a single table and doing a simple SELECT, start
thinking about multi-table situations.

In particular, how did you generate those 100M rows in the first
place? If you have a high append rate from multiple writers to one
long table, then you're going to be affected badly by the allocation
rate of an identity column. The big advantage of GUIDs is the trivial
parrallelization of their allocation algorithm.


> > Fouthly, Kimball recommended it and he does rather know what he's on
> > about.
>
> So? He's one person. He has his opinions.

He's also a Lensman and has a natty line in grey leather jodhpurs.


> But what you're saying is that MySQL, Microsoft, IBM and others know
> less than this one guy?

Very much so! They don't go to Jerry Stuckle for advice, but they
certainly do go to Kimball.


> But even ANSI agrees that identity columns are a good thing.

Of course they are - they're far better than using max(). GUIDs are an
even better approach (in almost all circumstances). They're a
massively better approach for some, and they're almost never harmful
either.
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:
> On 5 Feb, 23:02, Jerry Stuckle <jstuck... RemoveThis @attglobal.net> wrote:
>
>>> If you're going to read the last value returned by an identity column,
>>> do it within SQL and do it within some sort of atomic transaction. The
>>> idea of setting it in one line of PHP and then reading it in another
>>> line is just icky. Who's to say another process hasn't incremented the
>>> thing in the meantime?
>> Because mysql_insert_id() is guaranteed to get the id of the last
>> insertion of the current connection.
>
> That's certainly better than it being global to PHP, but it's still
> not perfect.
>

It is perfect.

> 1. It's just not _necessary_ (do it from SQL, even return it from the
> SQL stored procedure afterwards if you want to print it out
> afterwards). This is PHP's ghastly "swiss army knife" design principle
> where any random dregs get added to the language "just in case they
> come in handy", not from any sensible design principle.
>

No, the mysql_insert_is() is a MySQL call, not PHP.

> 2. It encourages PHP code that's tightly coupled to MySQL, not to a
> generic SQL back end. This example isn't rocket science, there's no
> need for it to start tying itself into platform-specific features.
> Leave the DB code in the SQL and build a better, more generic, more re-
> usable PHP product.
>

Look around, Andy. Every database nowadays has it's own extensions to
SQL. For instance, in Access and MS SQL, you SELECT @@IDENTITY. DB2
has SELECT IDENTITY_VAL_LOCAL() (last I looked Oracle didn't have such a
column, but that's been a while). Nowadays you're going to be more
tightly coupled to a database because there are differences.

Of course, there are other options, i.e. PDO, which isolate your
application more from the database.

The real problem here is the ANSI SQL spec is lacking, so rdbms
developers have had to come up with their own ways of doing it.

> 3. It's separable from the PHP call that executes the SQL query and
> actually allocates the identity. In this example they're on adjacent
> lines, but what happens if they start being a screenful apart? This
> is how working brittle code turns into non-working broken code over
> times.
>

Still no problem. The last_insert_id() is guaranteed to contain the id
of the last inserted record on this connection. And since any other
insert is on another connection, there won't be a problem.

> 4. Connection pooling. A pooled DB connection can certainly break this
> scope model, if you make two allocating operations on it before
> reading the identity value(s).
>

Only if the pool is broken. When a script requests a connection from
the pool, it should have that connection until the script releases it
back to the pool. If the pool is allowing an active connection to be
used by another script, the pool is broken.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex RemoveThis @attglobal.net
==================
 >> Stay informed about: Any SQL experts here?