(mysql.info) query-speed

 
 7.2 Optimizing `SELECT' and Other Statements
 ============================================

 
* explain                      Optimizing Queries with `EXPLAIN'
* estimating-performance       Estimating Query Performance
* select-speed                 Speed of `SELECT' Queries
* where-optimizations          `WHERE' Clause Optimization
* range-optimization           Range Optimization
* index-merge-optimization     Index Merge Optimization
* is-null-optimization         `IS NULL' Optimization
* distinct-optimization        `DISTINCT' Optimization
* left-join-optimization       `LEFT JOIN' and `RIGHT JOIN' Optimization
* nested-joins                 Nested Join Optimization
* outer-join-simplification    Outer Join Simplification
* order-by-optimization        `ORDER BY' Optimization
* group-by-optimization        `GROUP BY' Optimization
* limit-optimization           `LIMIT' Optimization
* how-to-avoid-table-scan      How to Avoid Table Scans
* insert-speed                 Speed of `INSERT' Statements
* update-speed                 Speed of `UPDATE' Statements
* delete-speed                 Speed of `DELETE' Statements
* tips                         Other Optimization Tips
 
 First, one factor affects all statements: The more complex your
 permissions setup, the more overhead you have. Using simpler
 permissions when you issue `GRANT' statements enables MySQL to reduce
 permission-checking overhead when clients execute statements. For
 example, if you do not grant any table-level or column-level
 privileges, the server need not ever check the contents of the
 `tables_priv' and `columns_priv' tables. Similarly, if you place no
 resource limits on any accounts, the server does not have to perform
 resource counting. If you have a very high statement-processing load,
 it may be worth the time to use a simplified grant structure to reduce
 permission-checking overhead.
 
 If your problem is with a specific MySQL expression or function, you
 can perform a timing test by invoking the `BENCHMARK()' function using
 the `mysql' client program. Its syntax is
 `BENCHMARK(LOOP_COUNT,EXPRESSION)'.  The return value is always zero,
 but `mysql' prints a line displaying approximately how long the
 statement took to execute. For example:
 
      mysql> SELECT BENCHMARK(1000000,1+1);
      +------------------------+
      | BENCHMARK(1000000,1+1) |
      +------------------------+
      |                      0 |
      +------------------------+
      1 row in set (0.32 sec)
 
 This result was obtained on a Pentium II 400MHz system. It shows that
 MySQL can execute 1,000,000 simple addition expressions in 0.32 seconds
 on that system.
 
 All MySQL functions should be highly optimized, but there may be some
 exceptions. `BENCHMARK()' is an excellent tool for finding out if some
 function is a problem for your queries.

Info Catalog

(mysql.info) optimize-overview

(mysql.info) optimization

(mysql.info) locking-issues

automatically generated byinfo2html

(mysql.info) query-speed

Menu