Select Statement Optimization
suggest changeBelow are some tips to remember while we are writing a select query in MySQL that can help us and reduce our query time:-
- Whenever we use where in a large table we should make sure the column in where clause are index or not. Ex:- Select * from employee where user_id > 2000. user_id if indexed then will speed up the evaluation of the query atlot. Indexes are also very important during joins and foreign keys.
- When you need the smaller section of content rather then fetching whole data from table, try to use limit. Rather then writing Ex:- Select * from employee. If you need just first 20 employee from lakhs then just use limit Ex:- Select * from employee LIMIT 20.
- You can also optimize your query by providing the column name which you want in resultset. Rather then writing Ex:- Select * from employee. Just mention column name from which you need data if you table has lots of column and you want to have data for few of them. Ex:- Select id, name from employee.
- Index column if you are using to verify for NULL in where clause. If you have some statement as SELECT * FROM tbl_name WHERE key_col IS NULL; then if key_col is indexed then query will be evaluated faster.
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents