MySQL Mysql Performance Tips Select Statement Optimization
Example
Below 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.
