5 tips for optimizing mysql queries

mysql

 

  1. use the explain command
  2. use less complex permissions
  3. specific mysql functions can be tested using the built-in "benchmark" command
  4. optimize where clauses
  5. use statement priorities

1. use the explain command

  • Use multiple-row INSERT statements to store many rows with one SQL statement.
  • The explain command can tell you which indexes are used with the specified query and many other pieces of useful information that can help you choose a better index or query.

2. use less complex permissions

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.

3. specific mysql functions can be tested using the built-in "benchmark" command

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

4. optimize where clauses

  • Remove unnecessary parentheses
  • COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.
  • If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table

5. use statement priorities

  • Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
    Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.

Latest from Ciprian


attitude blog

Feedback
My name is

 

Phone

 

Email

 

URL

 

Project Summary (details, timeline, budget, etc.)

 

Attach File (.doc, .docx, .pdf, .txt, .jpg only)