DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) Query Speed

Info Catalog (mysql.info.gz) Optimize Overview (mysql.info.gz) MySQL Optimization (mysql.info.gz) Locking Issues
 
 7.2 Optimizing `SELECT' Statements and Other Queries
 ====================================================
 
 First, one factor affects all statements: The more complex your
 permission setup is, the more overhead you will have.
 
 Using simpler permissions when you issue `GRANT' statements enables
 MySQL to reduce permission-checking overhead when clients execute
 statements.  For example, if you don't 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 query volume, it may be
 worth the time to use a simplified grant structure to reduce
 permission-checking overhead.
 
 If your problem is with some specific MySQL expression or function, you
 can use the `BENCHMARK()' function from the `mysql' client program to
 perform a timing test. Its syntax is
 `BENCHMARK(LOOP_COUNT,EXPRESSION)'. 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 very optimized, but there may be some
 exceptions. `BENCHMARK()' is a great tool to find out if this is a
 problem with your query.
 

Menu

 
* EXPLAIN                     `EXPLAIN' Syntax (Get Information About a `SELECT')
* Estimating performance      Estimating Query Performance
* SELECT speed                Speed of `SELECT' Queries
* Where optimizations         How MySQL Optimizes `WHERE' Clauses
* Range optimization          Range Optimization
* Index Merge optimization    Index Merge Optimization
* IS NULL optimization        How MySQL Optimizes `IS NULL'
* DISTINCT optimization       How MySQL Optimizes `DISTINCT'
* LEFT JOIN optimization      How MySQL Optimizes `LEFT JOIN' and `RIGHT JOIN'
* ORDER BY optimization       How MySQL Optimizes `ORDER BY'
* GROUP BY optimization       How MySQL Optimizes `GROUP BY'
* LIMIT optimization          How MySQL Optimizes `LIMIT'
* 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
 
Info Catalog (mysql.info.gz) Optimize Overview (mysql.info.gz) MySQL Optimization (mysql.info.gz) Locking Issues
automatically generated byinfo2html