MySQL AB :: MySQL Presentations: Optimizing MySQL

* SELECT SQL_BUFFER_RESULTS ...
Will force MySQL to make a temporary result set. As soon as the temporary set is done, all locks on the tables are released. This can help when you get a problem with table locks or when it takes a long time to transfer the result to the client.

* SELECT SQL_SMALL_RESULT ... GROUP BY ...
To tell the optimizer that the result set will only contain a few rows.

* SELECT SQL_BIG_RESULT ... GROUP BY ...
To tell the optimizer that the result set will contain many rows.

* SELECT STRAIGHT_JOIN ...
Forces the optimizer to join the tables in the order in which they are listed in the FROM clause.

* SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
Forces MySQL to use/ignore the listed indexes.