As a three year experienced database engineer, tuning database and SQL almost becomes my daily work. Here are some tips for database and SQL tuning.
For SQL Tuning:
1. Using Group by rather than DISTINCT:
As DISTINCT order beforehand, then, organize the data into an ordered set, the sorting procedure may takes more time consumption than group by.
2. Using EXLAIN to view SQL Execution Status:
Aware of whether all indexes are properly used during join procedure, if not, you may not require that index. You may remove unused index to increase insertion performance. In where clause, do not use stored procedure or string concat on equation comparison, which will corrupt the indexes usage.
3. How many indexes you should use on one table?
Well, it depends, an index may increase query performance while querying and joining tables.But index may be space consumption (Double the size used for each column indexed), and delay insertion speed.
4. Using result set insertion rather than prepared statement, and using prepared statement instead of executing query:
That is, SQL syntax parsing and interpreting is the bottleneck while executing SQL statement. Avoiding SQL syntax parsing may well increase the execution performance.
5. Understanding the differences within scan method, join method and join order:
Scan method has: Sequential Scan, Index Scan, bitmap Scan
- Sequential Scan: Best for scanning small size table.
- Index Scan: Using B-tree index to scan big size table.
- Bitmap Scan: Best for combined index
Join Method has: Nested loop, Hash Join, Merge Join
- Nested Loop: Best for two tables have related indexes. Using two for-loops to join tables. Best for small table joining.
- Hash Loop: Building hash table (Initial Setup) is time consumption, but once the hash is built, the joining will be much faster. Hash are built in memory, so be sure the memory can contains the amount of hashs
- Merge Join: Ideal for large table, need to sort data before joining.
Join order: Aware whether the most effective index is used.
