Database Reference
In-Depth Information
Example 1:
The following example illustrates:
If the inner and outer queries return
M
and
N
rows, respectively, the
execution time becomes on the order of
f(M×N)
, rather than
f(M+N)
as
it would be for an uncorrelated subquery. An implication is that query
construct with
IN
(<sub-query>) can be much slower than a query written
using an
IN(<value-list>)
construct that lists the same values that the
sub-query would return. Obviously, this is a very undesirable situation.
4.
The optimizer is more mature for joins than for subqueries,
so in many cases a statement that uses a subquery can be
executed more efficiently if you rewrite it as a join. An exception
to this occurs for the case where an IN (<sub-query>) construct
can be rewritten as a SELECT DISTINCT join construct.
Example 2:
The following example illustrates:
Note however, that in this case, the join requires an extra
DISTINCT
operation
and is not more efficient than the subquery. If you are not looking for distinct
values in the result set, then the
Select
statement with the join is preferred to
the
Select
statement with a subquery.
19.4.3 Limitations on server-side Cursors
Server-side cursors are implemented in the C programming language via the
mysql_stmt_attr_set()
function. The same implementation is used for cursors in stored
routines. A server-side cursor allows a result set to be generated on the server side only;
the entire result set is not necessarily transferred to the client, but only for the rows
requested by the client. For example, if a client executes a query but is only interested
in a few rows, the remaining rows are not transferred. In MySQL, a server-side cursor is
materialized into a temporary table. Initially, this is a memory table, but is converted to
disk table if its size reaches the value of the
max_heap_table_size
system variable.
One limitation of the implementation is that for a large result set, retrieving its rows
through a cursor might be slow.
