I've got a set of transactions for storing stuff in a database which runs
slower and slower and slower as the tables get larger.

The traffic graph in MySQL Administrator goes up and up as the Number of SQL
Queries graph goes down and down.

There is nothing obviously wrong with any of the queries in the
transaction - they've all got sensible indexes, and none of them returns any
significant amount of data, which is why the bytes_sent figure (the Traffic)
graph is puzzling.

(I haven't yet checked running MySQL and the client on separate boxes and
getting a network trace of the traffic between them - I'll do that if I have
to, in order to find out whether bytes_sent is lying and that much data is
not being returned to the client, as indicated by examination of the queries
being performed, or whether I've wrongly analysed the queries, and lots of
data is being sent, and bytes_sent is telling the truth.)

Now, another odd thing is what turns up in the slow transaction log, being
lots of instances of:

# Time: 070123 9:25:25
# User@Host: xx[xx] @ localhost [127.0.0.1]
# Query_time: 2 Lock_time: 0 Rows_sent: 107772 Rows_examined: 107772
SET last_insert_id=107772;
SELECT LAST_INSERT_ID() FROM xx;

Eh?? Surely LAST_INSERT_ID() can't take any time?? What's going on here?? -
I have to be able to insert millions of rows into this table, and can't have
it taking seconds to do LAST_INSERT_ID() each time! I have trouble imagining
why LAST_INSERT_ID() does anything other than take a number from memory from
a data structure in the server belonging to the connection.

The main log seems to confirm what the slow log is saying - most of the
timestamps are after fetches of LAST_INSERT_ID():

65 Query SELECT LAST_INSERT_ID() FROM xx
070123 9:27:26 3 Query SHOW STATUS
3 Query SHOW INNODB STATUS
070123 9:27:27 3 Query SHOW STATUS
3 Query SHOW INNODB STATUS
070123 9:27:28 3 Query SHOW STATUS
3 Query SHOW INNODB STATUS

--
Tim Ward
Brett Ward Limited - www.brettward.co.uk

"Tim Ward" <EMAIL REMOVED> wrote in message
news:EMAIL REMOVED...
>
> SELECT LAST_INSERT_ID() FROM xx;

Whoops. The person who wrote the Java that generated the above was under the
impression that the server kept the last insert id on a per table basis,
rather than per connection as it actually does. (So long since I've written
a query like that by hand that I didn't spot it immediately.) Leaving out
the "FROM xx" fixes it.

--
Tim Ward
Brett Ward Limited - www.brettward.co.uk

>> SELECT LAST_INSERT_ID() FROM xx;
>
> Whoops. The person who wrote the Java that generated the above was under the
> impression that the server kept the last insert id on a per table basis...

There is an alternative way of getting the last inserted ID:

SELECT * FROM tbl_name WHERE auto_col IS NULL

(see http://dev.mysql.com/doc/refman/4.1/...ate-table.html)

I haven't tested it, but maybe this does work on a per-table basis.

--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Willem Bogaerts wrote:
>>> SELECT LAST_INSERT_ID() FROM xx;
>> Whoops. The person who wrote the Java that generated the above was under the
>> impression that the server kept the last insert id on a per table basis...
>
> There is an alternative way of getting the last inserted ID:
>
> SELECT * FROM tbl_name WHERE auto_col IS NULL
>
> (see http://dev.mysql.com/doc/refman/4.1/...ate-table.html)
>
> I haven't tested it, but maybe this does work on a per-table basis.
>

I would not expect this to be connection-specific. That is, if two
transactions insert into the same table then issue this command, I would
expect both to get the results of the second insert (if it works at all).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================

"Tim Ward" <EMAIL REMOVED> wrote:
> "Tim Ward" <EMAIL REMOVED> wrote in message
> news:EMAIL REMOVED...
>>
>> SELECT LAST_INSERT_ID() FROM xx;
>
> Whoops. The person who wrote the Java that generated the above was under the
> impression that the server kept the last insert id on a per table basis,
> rather than per connection as it actually does. (So long since I've written
> a query like that by hand that I didn't spot it immediately.) Leaving out
> the "FROM xx" fixes it.

Exactly. With the FROM clause the query seems to do a dummy read of the
whole table (hence the high numer of examined rows in the slow log).
I guess this is bug. What version of MySQL are you using?


If you need the last little bit of performance, you should use the
last_insert_id() function from the MySQL client API. For Connector/J
that would be getGeneratedKeys(), for C mysql_insert_id().

http://dev.mysql.com/doc/refman/5.0/...last-insert-id
http://dev.mysql.com/doc/refman/5.0/...insert-id.html


The difference is the following: if you issue an INSERT or UPDATE
statement that affects a AUTO_INCREMENT column, the MySQL server sends
the generated value back to the client immediately (in the OK packet).
The client remembers this value und tells you if you use the API
function named above. SELECT LAST_INSERT_ID() OTOH is just a normal
SQL statement executed by the server. It requires a roundtrip to the
MySQL server and some parse time there.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Jerry Stuckle wrote:
> I would not expect this to be connection-specific. That is, if two
> transactions insert into the same table then issue this command, I would
> expect both to get the results of the second insert (if it works at all).

That's not the way it works. LAST_INSERT_ID() returns the last
auto-generated primary key value that was generated during the _current_
connection only, even if other connections generate subsequent values.

Say connection A inserts and generates a primary key value 123, and
connection B inserts and generates a primary key value 124. On
connection A, you can query "SELECT LAST_INSERT_ID()" on both A and B as
many times as you like. A will get 123 repeatedly while B gets 124
repeatedly.

The two connections may are may not be inserting to the same table. It
doesn't affect the fact that the last insert id value is maintained per
connection.

Try it -- all you need is two DOS windows each running Mysql clients.

This is no doubt by design, so you don't have to worry about race
conditions.

Regards,
Bill K.

Bill Karwin wrote:
> Jerry Stuckle wrote:
>> I would not expect this to be connection-specific. That is, if two
>> transactions insert into the same table then issue this command, I
>> would expect both to get the results of the second insert (if it works
>> at all).
>
> That's not the way it works. LAST_INSERT_ID() returns the last
> auto-generated primary key value that was generated during the _current_
> connection only, even if other connections generate subsequent values.
>
> Say connection A inserts and generates a primary key value 123, and
> connection B inserts and generates a primary key value 124. On
> connection A, you can query "SELECT LAST_INSERT_ID()" on both A and B as
> many times as you like. A will get 123 repeatedly while B gets 124
> repeatedly.
>
> The two connections may are may not be inserting to the same table. It
> doesn't affect the fact that the last insert id value is maintained per
> connection.
>
> Try it -- all you need is two DOS windows each running Mysql clients.
>
> This is no doubt by design, so you don't have to worry about race
> conditions.
>
> Regards,
> Bill K.

Bill,

Please read the previous update. I know how last_insert_id() works, and
William was referring to another way. He discussed using:

SELECT * FROM tbl_name WHERE auto_col IS NULL

I would think this would return the last item inserted into the table,
no matter which transaction did it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================

>>> SELECT LAST_INSERT_ID() FROM xx;
>>
>> Whoops. The person who wrote the Java that generated the above was under the
>> impression that the server kept the last insert id on a per table basis,
>> rather than per connection as it actually does. (So long since I've written
>> a query like that by hand that I didn't spot it immediately.) Leaving out
>> the "FROM xx" fixes it.
>
>Exactly. With the FROM clause the query seems to do a dummy read of the
>whole table (hence the high numer of examined rows in the slow log).
>I guess this is bug. What version of MySQL are you using?

I disagree that it's a bug. If you issue the query:
SELECT 'x' FROM xx;
and table xx has ten million rows, then this select is *SUPPOSED TO*
return ten million copies of 'x'. A similar problem occurs if
you use last_insert_id() instead of 'x'.

Try:
SELECT LAST_INSERT_ID();

EMAIL REMOVED (Gordon Burditt) wrote:
>>
>>Exactly. With the FROM clause the query seems to do a dummy read of the
>>whole table (hence the high numer of examined rows in the slow log).
>>I guess this is bug. What version of MySQL are you using?
>
> I disagree that it's a bug. If you issue the query:
> SELECT 'x' FROM xx;
> and table xx has ten million rows, then this select is *SUPPOSED TO*
> return ten million copies of 'x'. A similar problem occurs if
> you use last_insert_id() instead of 'x'.

Misunderstanding. IMHO it's a bug to actually *read* the table. If you
ask the server SELECT <constant expression> FROM <table> this should be
handled like SELECT COUNT(*) FROM <table> and then sending back
<constant expression> as often as needed.

SELECT COUNT(*) is optimized in a special way and seldom needs to scan
the respective table.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Jerry Stuckle wrote:
> I would think this would return the last item inserted into the table,
> no matter which transaction did it.


D'oh! You're absolutely right. Mea culpa. I replied without reading
the context of the thread. Pay no attention to me, I don't have as much
time for the newsgroups as I used to.

Bill K.