Professional Web Applications Themes

LAST_INSERT_ID() performance?? - MySQL

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 ...

  1. #1

    Default LAST_INSERT_ID() performance??

    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 ysed 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
    # UserHost: 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 Guest

  2. #2

    Default Re: LAST_INSERT_ID() performance??

    "Tim Ward" <com> wrote in message
    news:individual.net... 

    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


    Tim Guest

  3. #3

    Default Re: LAST_INSERT_ID() performance??

    >> SELECT LAST_INSERT_ID() FROM xx; 

    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/en/create-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 Guest

  4. #4

    Default Re: LAST_INSERT_ID() performance??

    Willem Bogaerts wrote: 
    >> 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...[/ref]
    >
    > 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/en/create-table.html)
    >
    > I haven't tested it, but maybe this does work on a per-table basis.
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: LAST_INSERT_ID() performance??

    "Tim Ward" <com> wrote: 
    >
    > 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.[/ref]

    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/en/connector-j-usagenotes-basic.html#connector-j-usagenotes-last-insert-id
    http://dev.mysql.com/doc/refman/5.0/en/mysql-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 p 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/
    Axel Guest

  6. #6

    Default Re: LAST_INSERT_ID() performance??

    Jerry Stuckle wrote: 

    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 Guest

  7. #7

    Default Re: LAST_INSERT_ID() performance??

    Bill Karwin wrote: 
    >
    > 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.[/ref]

    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.
    net
    ==================
    Jerry Guest

  8. #8

    Default Re: LAST_INSERT_ID() performance??

    >>> SELECT LAST_INSERT_ID() FROM xx; 
    >
    >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?[/ref]

    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();

    Gordon Guest

  9. #9

    Default Re: LAST_INSERT_ID() performance??

    org (Gordon Burditt) wrote: 
    >
    > 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'.[/ref]

    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/
    Axel Guest

  10. #10

    Default Re: LAST_INSERT_ID() performance??

    Jerry Stuckle wrote: 


    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.
    Bill Guest

Similar Threads

  1. Creating a session variable to trap Last_Insert_ID
    By Shane930 in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 3rd, 05:06 AM
  2. Replies: 1
    Last Post: October 9th, 12:17 PM
  3. SELECT last_insert_id() not working
    By yawnmoth in forum MySQL
    Replies: 3
    Last Post: October 8th, 11:28 AM
  4. last_insert_id()
    By lingo_user in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 29th, 07:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139