a) Proper data filteration (with the help of where clause)
b) Use Explain with select statements to verify the Execution plan of query. If MySQL is doing full table scan then create index on columns being used in Where clause.
c) Create index on searching fields.
d) Use column names instead of * in select Statement. If we use * then MySQL will get the column details from information_schema database and then display the all columns from table. Some columns may not needed in query so its recommended to use specific columns with select statement. Doing this will give limited result, use minimal resources and reduce network traffic.
e) Don’t create index blindly on which fields, searching is not required. As this index is really not required but each time any row will be inserted or updated this index still will be update and kill the db performance.
f) Avoid duplication of index.
For example idx_order_id_cust_id(order_id,cust_id) is enough for order_id field also, so no need separate index for order_id. But this index will not work for cust_id field so we have to make separate index for this field if required.
Because index works in same order like index on 3 fields order_id, cust_id, product_id will work for (where order_id=? and where order_id=? and cust_id=?) but it will not work for (where cust_id=?) or (where cust_id=? and product_id=?).
g) Avoid sub-query and use join instead of sub-query (it is beneficial in Max. cases but not always, some time sub-query is beneficial in optimization).
h) Avoid use of like. (Specially like ‘%abc%’ as it will not use index even index is there on that field).
i) Columns data type and its length should be least as requried instead of blindly more than the required (For example c_customer_fname varchar(256) here varchar(50) should be sufficient).
j) Avoid use of functions how much can be avoided. (for example lower(c_fname)=’zafar’, here no need of lower() function as string search is not case sensitive, so c_name=’zafar’ will work.)
k) Avoid function on searching field as it will kill the performance.
for example: If order table contains 89 Lacs or more records and we need all order details after order_date (timestamp field and index is already there) as 1-Nov-13.
Slow query: select order_id, order_date, order_value from order where date(order_date)>= ’2013-11-01′
Optimized Query: select order_id, order_date, order_value from order where order_date>= ’2013-11-01 00:00:00′
l) Avoid use of triggers.
m) Always update/delete records based on primary key to avoid locking.
n) Use bulk insert instead of single insert statements. LOAD DATA infile is 10 times faster than Insert.
o) Limit resultset using limit keyword if limited rows are required. This will be faster and reduce network traffic.
p) Minimize the OR keyword in your where clause.
q) Avoid data fetching from information schema database in your queries (Mysql).
r) Declare columns to be not null if possible to do query fast.
s) use of Table partitioning.
t) Proper Normalization.
Thumb Rule of Index:
a) In any query, myqsl use only single index per table based on its own intelligency as per index cardinality, generally which is the best index for the query. But some time (in rearest to rear case) it can be wrong. At that time we can force the suitable index.
b) In case of combined index, mysql used index in same order as created.
For example if a table has a combined index idx_order_cust_product(order_id,cust_id,product_id) then
select order_id,cust_id,product_id from order_table where order_id=5 — It will use index.
select order_id,cust_id,product_id from order_table where order_id=5 and cust_id=7 — It will also use index.
select order_id,cust_id,product_id from order_table where order_id=5 and cust_id=7 and product_id=9 — It will also use index.
select order_id,cust_id,product_id from order_table where cust_id=7 — It will not use index.
select order_id,cust_id,product_id from order_table where product_id=9 — It will also not use index.
select order_id,cust_id,product_id from order_table where cust_id=7 and product_id=9 — It will also not use index.
select order_id,cust_id,product_id from order_table where product_id=9 and order_id=5 – It will also not use index.