MySQL Query Optimization

http://www.informit.com/articles/article.aspx?p=377652

Match index types to the type of comparisons you perform. When you create an index, most storage engines choose the index implementation they Match index types to the type of comparisons you perform. When you create an index, most storage engines choose the index implementation they will use. For example, InnoDB always uses B-tree indexes. MySQL also uses B-tree indexes, except that it uses R-tree indexes for spatial data types. However, the MEMORY storage engine supports hash indexes and B-tree indexes , and allows you to select which one you want. To choose an index type, consider what kind of comparison operations you plan to perform on the indexed column:

• For a hash index, a hash function is applied to each column value. The resulting hash values are stored in the index and used to perform lookups. (A hash function implements an algorithm that is likely to produce distinct hash values for distinct input values. The advantage of using hash values is that they can be compared more efficiently than the original values.) Hash in dexes are very fast for exact-match comparisons performed with the = or <=> operators . But they are poor for comparisons that look for a range of values, as in these expressions:

• id < 30
  
  weight BETWEEN 100 AND 150

• B-tree indexes can be used effectively for comparisons involving exact or range-based comparisons that use the < , <= , = , >= , > , <> , != , and BETWEEN operators. B-tree indexes can also be used for LIKE pattern matches if the pattern begins with a literal string rather than a wildcard character.

If you use a MEMORY table only for exact-value lookups, a hash index is a good choice. This is the default index type for MEMORY tables , so you need do nothing special. If you need to perform range-based comparisons with a MEMORY table, you should use a B-tree index instead. To specify this type of index, add USING BTREE to your index definition. For example:

CREATE TABLE lookup

(

  id   INT NOT NULL,

  name  CHAR(20),

  PRIMARY KEY USING BTREE (id)


) ENGINE = MEMORY;

If the types of statements that you expect to execute warrant it, a single MEMORY table can have both hash indexes and B-tree indexes, even on the same column.

Some types of comparisons cannot use indexes. If you perform comparisons only by passing column values to a function such as STRCMP() , there is no value in indexing it. The server must evaluate the function value for each row, which precludes use of an index on the column.

 

Use the slow-query log to identify queries that may be performing badly. This log can help you find queries that might benefit from indexing. You can view this log directly (it is written as a text file), or use the mysqldumpslow utility to summarize its contents. (See Chapter 11, "General MySQL Administration," for a discussion of MySQL's log files.) If a given query shows up over and over in the slow-query log, that's a clue you've found a query that might not be written optimally. You may be able to rewrite it to make it run more quickly. Keep in mind when assessing your slow-query log that "slow" is measured in real time, so more queries will show up in the slow-query log on a heavily loaded server than on a lightly loaded one.

 

 

Use EXPLAIN to verify optimizer operation. The EXPLAIN statement can tell you whether indexes are being used. This information is helpful when you're trying different ways of writing a statement or checking whether adding indexes actually will make a difference in query execution efficiency. For examples, see "Using EXPLAIN to Check Optimizer Operation."

Give the optimizer hints when necessary. Normally, the MySQL optimizer considers itself free to determine the order in which to scan tables to retrieve rows most quickly. On occasion, the optimizer will make a non-optimal choice. If you find this happening, you can override the optimizer's choice using the STRAIGHT_JOIN keyword. A join performed with STRAIGHT_JOIN is like a cross join but forces the table s to be joined in the order named in the FROM clause .

STRAIGHT_JOIN can be specified at two points in a SELECT statement. You can specify it between the SELECT keyword and the selection list to have a global effect on all cross joins in the statement, or you can specify it in the FROM clause. The following two statements are equivalent:

SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ;

SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3
 ... ;

note: from envykok

force t1 join t2 join t3

Run the query both with and without STRAIGHT_JOIN ; MySQL might have some good reason not to use indexes in the order you think is best. (Check the execution plans with EXPLAIN to see how MySQL handles each statement.)

You can also use FORCE INDEX , USE INDEX , or IGNORE INDEX to give the server guidance about which indexes to prefer.