The BENCHMARK() function
executes the expression expr
repeatedly count times. It may be
used to time how quickly MySQL processes the expression. The
result value is always 0. The intended use
is from within the mysql client, which
reports query execution times:

The time reported is elapsed time on the client end, not CPU
time on the server end. It is advisable to execute
BENCHMARK() several times, and
to interpret the result with regard to how heavily loaded the
server machine is.

BENCHMARK() is intended for
measuring the runtime performance of scalar expressions, which
has some significant implications for the way that you use it
and interpret the results:

Only scalar expressions can be used. Although the
expression can be a subquery, it must return a single
column and at most a single row. For example,
BENCHMARK(10, (SELECT * FROM
t)) will fail if the table t
has more than one column or more than one row.

Executing a SELECT
expr statement
N times differs from executing
SELECT BENCHMARK(N,
expr) in terms of the
amount of overhead involved. The two have very different
execution profiles and you should not expect them to take
the same amount of time. The former involves the parser,
optimizer, table locking, and runtime evaluation
N times each. The latter
involves only runtime evaluation
N times, and all the other
components just once. Memory structures already allocated
are reused, and runtime optimizations such as local
caching of results already evaluated for aggregate
functions can alter the results. Use of
BENCHMARK() thus measures
performance of the runtime component by giving more weight
to that component and removing the “noise”
introduced by the network, parser, optimizer, and so
forth.

The example illustrates that although the client specified a
user name of davida (as indicated by the
value of the USER() function),
the server authenticated the client using an anonymous user
account (as seen by the empty user name part of the
CURRENT_USER() value). One way
this might occur is that there is no account listed in the
grant tables for davida.

CURRENT_USER() was added in
MySQL 4.0.6. As of MySQL 4.1.0, the string uses the
utf8 character set.

Returns the default (current) database name. As of MySQL 4.1,
the string uses the utf8 character set. If
there is no default database,
DATABASE() returns
NULL as of MySQL 4.1.1, and the empty
string before that.

A SELECT statement may include
a LIMIT clause to restrict the number of
rows the server returns to the client. In some cases, it is
desirable to know how many rows the statement would have
returned without the LIMIT, but without
running the statement again. To obtain this row count, include
a SQL_CALC_FOUND_ROWS option in the
SELECT statement, and then
invoke FOUND_ROWS() afterward:

The second SELECT returns a
number indicating how many rows the first
SELECT would have returned had
it been written without the LIMIT clause.

In the absence of the SQL_CALC_FOUND_ROWS
option in the most recent successful
SELECT statement,
FOUND_ROWS() returns the number
of rows in the result set returned by that statement. If the
statement includes a LIMIT clause,
FOUND_ROWS() returns the number
of rows up to the limit. For example,
FOUND_ROWS() returns 10 or 60,
respectively, if the statement includes LIMIT
10 or LIMIT 50, 10.

The row count available through
FOUND_ROWS() is transient and
not intended to be available past the statement following the
SELECT SQL_CALC_FOUND_ROWS statement. If
you need to refer to the value later, save it:

If you are using SELECT
SQL_CALC_FOUND_ROWS, MySQL must calculate how many
rows are in the full result set. However, this is faster than
running the query again without LIMIT,
because the result set need not be sent to the client.

SQL_CALC_FOUND_ROWS and
FOUND_ROWS() can be useful in
situations when you want to restrict the number of rows that a
query returns, but also determine the number of rows in the
full result set without running the query again. An example is
a Web script that presents a paged display containing links to
the pages that show other sections of a search result. Using
FOUND_ROWS() enables you to
determine how many other pages are needed for the rest of the
result.

The use of SQL_CALC_FOUND_ROWS and
FOUND_ROWS() is more complex
for UNION statements than for
simple SELECT statements,
because LIMIT may occur at multiple places
in a UNION. It may be applied
to individual SELECT statements
in the UNION, or global to the
UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for
UNION is that it should return
the row count that would be returned without a global
LIMIT. The conditions for use of
SQL_CALC_FOUND_ROWS with
UNION are:

The SQL_CALC_FOUND_ROWS keyword must
appear in the first SELECT
of the UNION.

LAST_INSERT_ID() (with no
argument) returns the first automatically
generated value that was set for an
AUTO_INCREMENT column by the most
recently executedINSERT or
UPDATE statement to affect such
a column. For example, after inserting a row that generates an
AUTO_INCREMENT value, you can get the value
like this:

The currently executing statement does not affect the value of
LAST_INSERT_ID(). Suppose that
you generate an AUTO_INCREMENT value with
one statement, and then refer to
LAST_INSERT_ID() in a
multiple-row INSERT statement
that inserts rows into a table with its own
AUTO_INCREMENT column. The value of
LAST_INSERT_ID() will remain
stable in the second statement; its value for the second and
later rows is not affected by the earlier row insertions.
(However, if you mix references to
LAST_INSERT_ID() and
LAST_INSERT_ID(expr),
the effect is undefined.)

If the previous statement returned an error, the value of
LAST_INSERT_ID() is undefined.
For transactional tables, if the statement is rolled back due
to an error, the value of
LAST_INSERT_ID() is left
undefined. For manual
ROLLBACK,
the value of LAST_INSERT_ID()
is not restored to that before the transaction; it remains as
it was at the point of the
ROLLBACK.

The ID that was generated is maintained in the server on a
per-connection basis. This means that the
value returned by the function to a given client is the first
AUTO_INCREMENT value generated for most
recent statement affecting an
AUTO_INCREMENT column by that
client. This value cannot be affected by other
clients, even if they generate
AUTO_INCREMENT values of their own. This
behavior ensures that each client can retrieve its own ID
without concern for the activity of other clients, and without
the need for locks or transactions.

The value of LAST_INSERT_ID()
is not changed if you set the
AUTO_INCREMENT column of a row to a
non-“magic” value (that is, a value that is not
NULL and not 0).

Important

If you insert multiple rows using a single
INSERT statement,
LAST_INSERT_ID() returns the
value generated for the first inserted
row only. The reason for this is to
make it possible to reproduce easily the same
INSERT statement against some
other server.

Although the second INSERT
statement inserted three new rows into t,
the ID generated for the first of these rows was
2, and it is this value that is returned by
LAST_INSERT_ID() for the
following SELECT statement.

If you use INSERT
IGNORE and the row is ignored, the
AUTO_INCREMENT counter is not incremented
and LAST_INSERT_ID() returns
0, which reflects that no row was inserted.
(Before MySQL 4.1, the AUTO_INCREMENT
counter is still incremented and
LAST_INSERT_ID() returns the
new value.)

If expr is given as an argument to
LAST_INSERT_ID(), the value of
the argument is returned by the function and is remembered as
the next value to be returned by
LAST_INSERT_ID(). This can be
used to simulate sequences:

You can generate sequences without calling
LAST_INSERT_ID(), but the
utility of using the function this way is that the ID value is
maintained in the server as the last automatically generated
value. It is multi-user safe because multiple clients can
issue the UPDATE statement and
get their own sequence value with the
SELECT statement (or
mysql_insert_id()), without
affecting or being affected by other clients that generate
their own sequence values.

User Comments

I was issuing my query "select sql_calc_found_rows statement1 union statement2 union statement3 limit whatever" and getting a count from found_rows() that was incorrect. It was running as if I had run the query as "union all" because it was returning duplicates. After rereading the union syntax page I reissued the query with parens around each individual statement. "(select sql_calc_found_rows statement1) union (statement2) union (statement3) limit whatever" This yielded the expected result.

manual says:"... The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT."

though that means 'select * from tableX limit 50' returns 50 as a result of 'Found_rows()' just because tableX has 50 records, and 'select * from tableX limit 10,10' will return also 50 and not 10 or 20 as a result!

If you are using the Python MySQLdb module, bear in mind that inserting multiple rows with executemany() does NOT count as a single operation for the purpose of last_insert_id(). The value returned (assuming success) will be the ID created for the LAST row in the list.

Using the LAST_INSERT_ID() to simulate a sequence as suggested above, will not work well when table is replicated, and access is randomly made for one of the replicated servers.To overcome this, one can use this method:1) Create a table with two columns: First is auto_incremented primary key. The second, is unique int.

Despite the warning above "you cannot use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET" it seems that in actual fact you can. This was certainly correct in 5.0, but 5.5 appears to have removed this limitation.

If You would like to get current auto_increment value for certain table You can use this query. Just one note: it's insecure to let remote user to have access to information_schema, so try to execute this with some separated local user.