MySQL Query Optimization Tips and Techniques

1. Choosing Which Version of MySQL
2. Don't join extra tables in MySQL Query.
3. Only get N records which are going to used. Don't get extra records from table which you are not using.
4. Don't fetch extra column from table which are not in use.
5. Don't add spam records in tables
6. Don't get it overload any table like 1GB Size, In this case shift data to another table.
7. Get the understanding of normalization.
8. Use Explain/DESCRIBE to know about table structure and Query. For Example:

DESCRIBE SELECT * FROM `users` WHERE username='web-tech' 

9. Partition your table (MySQL 5.1). Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key.
10. Partition can be achieve in three way 1. RANGE, HASH and List/Key.
11. Build your indexes to match the queries running. For Example:

    ALTER TABLE `users` ADD INDEX `profile_id` (`profile_id`)

    Know about MySQL Indexing
    
12. Use concatination indexing, if required (When searching like below).

    SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';

13. No indexing on column which not used in search.
14. Sometime you need to use OPTIMIZE Table. For Example:

    OPTIMIZE TABLE `users`

    Used for defragment tables and update the InnoDB fulltext index
15. Full Text Search can be used, if required.
    http://www.web-technology-experts-notes.in/2013/05/mysql-fulltext-search.html

Best Related Posts are Following:

1. Why error 524 a timeout occurred.
2. Free Boost Web Application By 20% in Simple Steps.
3. Improve Ajax Performance.
4. Reduce the loading time of website.