Performances Tuning

How would you optimize a slow running SQL query? List the SQL optimization techniques !

 Below are the list of optimization techniques can be used to increase the performance of SQL queries. 

•  > Create indexes on columns used frequently in WHERE clauses, JOIN conditions, and ORDER BY clauses to speed up searches.
•  > Avoid Using the SELECT * FROM. Select only the required columns. 
•  > Use WHERE clause to limit rows. 
•  > Analyse the query with EXPLAIN to find bottlenecks.
•  > Use LIMIT clause to restrict number of rows retrieved. 
•  > Ensure to use JOINS and use on the indexes columns. 
•  > Analyse the query with EXPLAIN, SHOW PLAN to find bottlenecks
•  > Use EXISTS instead of IN (when using subqueries). 
•  > Ensure to perform stats gathering on the table on regular intervals. 

 Eg : Slow Query : 

> SELECT * FROM JOBS WHERE JOB_ID=100;

     Optimized Query : 

> SELECT job_title, job_id, min_salary, max_salary from jobs WHERE job_id=100;