- Choosing Which Version of MySQL
- Don't join extra tables in MySQL Query.
- Only get N records which are going to used. Don't get extra records from table which you are not using.
- Don't fetch extra column from table which are not in use.
- Don't add spam records in tables
- Don't get it overload any table like 1GB Size, In this case shift data to another table.
- Get the understanding of normalization.
- Use Explain/DESCRIBE to know about table structure and Query. For Example:
- Partition your table (MySQL 5.1). Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key.
- Partition can be achieve in three way 1. RANGE, HASH and List/Key.
- Build your indexes to match the queries running. For Example:
ALTER TABLE `users` ADD INDEX `profile_id` (`profile_id`)Know about MySQL Indexing
- Use concatination indexing, if required (When searching like below).
SELECT * FROM table_name WHERE field_name1='text1' AND field_name2='text2';
- No indexing on column which not used in search.
- Sometime you need to use OPTIMIZE Table. For Example:
OPTIMIZE TABLE `users`Used for defragment tables and update the InnoDB fulltext index
- Full Text Search can be used, if required.
DESCRIBE SELECT * FROM `users` WHERE username='web-tech'