Use Keyword - Explain
EXPLAIN SELECT * FROM tbl_name WHERE 1 = 0
The output from EXPLAIN is:
+------------------+
| Comment |
+------------------+
| Impossible WHERE |
+------------------+
Normally, EXPLAIN returns more information than that, including information about the indexes that will be used to
scan tables, the types of joins that will be used, and estimates of the number of rows that will need to be scanned
from each table.
Compare columns that have the same type
Try to make indexed columns stand alone in comparisons
1.
Declare columns to be NOT NULL. This gives you faster processing and requires less storage. It will also
simplify queries sometimes because you don't need to check for NULL as a special case.
2.Use PROCEDURE ANALYSE(). If you have MySQL 3.23 or newer, run PROCEDURE ANALYSE() to see what it
tells you about the columns in your table:
SELECT * FROM tbl_name PROCEDURE ANALYSE()
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256)
3.Consider using ENUM columns. If you have a string column that contains only a limited number of distinct
values, consider converting it to an ENUM column. ENUM values can be processed quickly because they are
represented as numeric values internally
4.Use OPTIMIZE TABLE for tables that are subject to fragmentation. Tables that are modified a great deal,
particularly those that contain variable-length columns, are subject to fragmentation. Fragmentation is bad
because it leads to unused space in the disk blocks used to store your table. Over time, you must read more
blocks to get the valid rows, and performance is reduced. This is true for any table with variable-length
rows, but is particularly acute for BLOB columns because they can vary so much in size. Use of OPTIMIZE
TABLE on a regular basis helps keep performance on the table from degrading
5.· Shorter SQL statements are faster than longer statements because they involve less parsing on the part of
the server and because they can be sent over the network from the client to the server more quickly.
6.Let MySQL insert default values for you; don't specify columns in INSERT statements that will be assigned
the default value anyway. On average, your statements will be shorter, reducing the number of characters
sent over the network to the server. In addition, because the statements contain fewer values, the server
does less parsing and value conversion.
