Databases Reference
In-Depth Information
To work around this limitation, you can use a derived table, because MySQL materi-
alizes it as a temporary table. This effectively executes two queries: one
SELECT
inside
the subquery, and one multitable
UPDATE
with the joined results of the table and the
subquery. The subquery opens and closes the table before the outer
UPDATE
opens the
table, so the query will now succeed:
mysql>
UPDATE tbl
->
INNER JOIN(
->
SELECT type, count(*) AS cnt
->
FROM tbl
->
GROUP BY type
->
) AS der USING(type)
->
SET tbl.cnt = der.cnt;
Query Optimizer Hints
MySQL has a few optimizer hints you can use to control the query plan if you're not
happy with the one MySQL's optimizer chooses. The following list identifies these hints
and indicates when it's a good idea to use them. You place the appropriate hint in the
query whose plan you want to modify, and it is effective for only that query. Check the
MySQL manual for the exact syntax of each hint. Some of them are version-dependent.
The options are:
HIGH_PRIORITY
and
LOW_PRIORITY
These hints tell MySQL how to prioritize the statement relative to other statements
that are trying to access the same tables.
HIGH_PRIORITY
tells MySQL to schedule a
SELECT
statement before other statements
that might be waiting for table locks so they can modify data. In effect, it makes
the
SELECT
go to the front of the queue instead of waiting its turn. You can also
apply this modifier to
INSERT
, where it simply cancels the effect of a global
LOW
_PRIORITY
server setting.
LOW_PRIORITY
is the reverse: it makes the statement wait at the very end of the queue
if there are any other statements that want to access the tables—even if the other
statements are issued after it. It's rather like an overly polite person holding the
door at a restaurant: as long as there's anyone else waiting, it will starve itself! You
can apply this hint to
SELECT
,
INSERT
,
UPDATE
,
REPLACE
, and
DELETE
statements.
These hints are effective on storage engines with table-level locking, but you should
never need them on InnoDB or other engines with fine-grained locking and con-
currency control. Be careful when using them on MyISAM, because they can dis-
able concurrent inserts and greatly reduce performance.
The
HIGH_PRIORITY
and
LOW_PRIORITY
hints are a frequent source of confusion.
They do not allocate more or fewer resources to queries to make them “work
harder” or “not work as hard”; they simply affect how the server queues statements
that are waiting for access to a table.
