MySQL Introduction

Most joins are done in one sweep.

Very few normal joins require temporary tables. Joins involving a GROUP BY and an ORDER BY on something other than the GROUP BY part will create a (usually memory-based) temporary table.

Memory-based temporary tables have dynamic hashing.

MySQL has a user configurable key cache and a record cache to quickly scan tables. Open tables are cached in a table cache.

An index optimizer quickly finds which possible index to use with a specific WHERE clause.

A range optimizer finds the range for an index which will then be used to find the matching rows. The range optimizer can optimize queries that use a combination of >=, >, =, <=, < and LIKE (of type “keyword%”) on a key column. When many possible indexes can be used, the range optimizer will choose the index that matches the smallest number of rows.

A join optimizer rearranges the tables in a SELECT statement in the optimal order. In the rare case the MySQL join optimizer gets this wrong, one can force the optimizer to join the tables in a particular order with the STRAIGHT_JOIN keyword.

For each sub-join, a simple WHERE is constructed to get a fast WHERE evaluation for each sub-join and to skip records as soon as possible.

A WHERE optimizer removes constant conditions.

Early detection finds invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.

SQL functions are implemented through a highly optimized class library. Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items, and the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings by using malloc and free.

In queries of type

SELECT ... WHERE col IN (a,b,c,d,e,f,...)

the IN part is sorted and checked through binary searching.

MIN/MAX on an indexed column is done with one key fetch.The MIN/MAX optimizer can also find the best value when one has specified all preceding index columns in the WHERE condition:

SELECT MAX(index_part2) FROM tbl_name WHERE
index_part_1=constant;

COUNT(*) on a single table without a WHERE is retrieved directly from the table information.

If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree rather than the data file for greater speed.

Indexes are used to find rows matching

WHERE index_column LIKE "prefix%".

GROUP BY and DISTINCT are optimized by creating a temporary HEAP table with the GROUP BY (or whole row) as a unique index.

INSERT DELAYED inserts rows in a queue to the SQL server, which is useful if you are using MySQL for logging and can't afford to wait until the INSERT is completed.

Index compression is used to get smaller and faster indexes.