Книга: Fedora™ Unleashed, 2008 edition

Query Optimization

Query Optimization

The biggest speed-ups can be seen by reprogramming your SQL statements so they are more efficient. If you follow these tips, your server will thank you:

? Select as little data as possible. Rather than SELECT *, select only the fields you need.

? If you only need a few rows, use LIMIT to select the number you need.

? Declare fields as NOT NULL when creating tables to save space and increase speed.

? Provide default values for fields, and use them where you can.

? Be careful with table joins because they are the easiest way to write inefficient queries.

? If you must use joins, be sure you join on fields that are indexed. They should preferably be integer fields because these are faster than strings for comparisons.

? Find and fix slow queries. Add log-long-format and log-slow-queries = /var/log/slow-queries.log to your /etc/my.cnf file, under [mysqld], and MySQL can tell you the queries that took a long time to complete.

? Use OPTIMIZE TABLE tablename to defragment tables and refresh the indexes.

Оглавление книги