(reconstructed from archives i accidentally deleted the copy in my mailbox
Daniel Clark writes:
I don't see how LIMIT would make a difference. LAST_INSERT_ID() only returns one record.
But it's worth trying in a big loop to get timing numbers.
Well, I decided to test this all out and see what happened. From what my little test was able to determine, there is no difference in speed from doing:
SELECT LAST_INSERT_ID(); vs SELECT LAST_INSERT_ID() LIMIT 1;
I used 100,000 selects as my benchmark number (which on my box here took about 40 seconds per run to complete). Times were measured using unix time, and I averaged the user times and the difference was so small as to be negligable, then i decided to measure the same thing but with
SELECT LAST_INSERT_ID() FROM table; and it was approximately 25% slower (but im assuming as the table grew in length so would the gap in speed.
Conclusion: it makes no difference positive or negative including the LIMIT, but as Michael pointed out including the FROM clause causes a big penalty. I hope someone else finds this slightly interesting. For completeness im including the program i wrote to test this. Your mileage may vary, it needs a little configuring for your particular setup before you can run tests. (database name, user/password etc) you have to manually change the one line inside the loop to call whichever function you want to test. And its also handy to change the one print statement before the loop.
Eric
##### speed_test.pl ##### #!/usr/bin/perl -w # speed_test.pl # purpose of this little program is to test whether or not # LIMIT has any effect on LAST_INSERT_ID() queries use strict; use DBI;
# grab a handle
my $dbh = DBI->connect('DBI:mysql:host=localhost;database=cust',
'root', 'rootpw', {PrintError => 0,RaiseError => 1});
my ($ret, $sth, $new_id, $counter);sub create_table_test {
$ret = $dbh->do( qq{
CREATE TABLE speed_test
(id INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id) ) });
return 1;
}
sub insert_record {
$sth = $dbh->prepare(qq{INSERT into `speed_test`
(`id`)
VALUES
(?)});
$ret = $sth->execute('NULL');
return 1;
}
sub fetch_without_limit {
$sth = $dbh->prepare(qq{SELECT LAST_INSERT_ID()});
$ret = $sth->execute();
$new_id = $sth->fetchrow_array();
return 1;
}
sub fetch_with_limit {
$sth = $dbh->prepare(qq{SELECT LAST_INSERT_ID() LIMIT 1});
$ret = $sth->execute();
$new_id = $sth->fetchrow_array();
return 1;
}
sub fetch_without_limit_but_with_from {
$sth = $dbh->prepare(qq{SELECT LAST_INSERT_ID() FROM speed_test});
$ret = $sth->execute();
$new_id = $sth->fetchrow_array();
return 1;
}#$ret = create_table_test(); #uncomment this to create the test table, if needed (1st run).
$ret = insert_record();
print "Testing without LIMITs but with from\n";
for ($counter = 0; $counter < 100000 ; $counter++) {
$ret = fetch_without_limit_but_with_from();
}
print "Done!\n";
##### end speed_test.pl #####
_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar – get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
