MySQL query optimizer

1. Use the explain statement to see if the index is used:

EXPLAIN SELECT * from tb1_name WHERE 1 = 0

2. The WHERE clause below illustrates how this works. In the first row, the optimizer simplifies expression 4/2 to a value of 2, and then uses the index on My_col to quickly find values that are less than 2. In the second expression, MySQL must retrieve the My_col value of each row, multiply by 2, and compare the result to 4. No index is available because each value in the column is retrieved so that the left expression can be evaluated:

WHERE My_col < 4/2where my_col * 2 < 4

3. Do not use wildcard characters at the beginning of the like pattern . Sometimes, someone uses the following form of a WHERE clause to search for a string:

where col_name like "%string%" if you want to find the s t r i n g, regardless of whether it appears anywhere in the column, then this is the right thing to do. But do not add "%" to the sides of the string out of habit. If you are actually looking for a string that is only at the beginning of the column, you should not want the first "%" number. For example, if you look for a last name in a column that contains a last name, you should write the WHERE clause:  where last_name like "mac%" Optimizer considers the beginning of the text portion of the pattern, and then uses the index to find the rows that match. Rather, it is preferable to write the following expression, which allows the use of an index on last_name:  WHERE last_name >= "Mac" and Last_Name < "Mad" do not work with pattern matching using the regexp operator.

MySQL query optimizer