Optimizing SELECT statements

Select queries are used to retrieve data in dynamic web pages, so tuning these statements provides good performance which is quite important. Here are some considerations to optimize queries:

• Make sure that you have indexes in tables. Indexes always help to speed up the filtering and the retrieval of results. We can specify indexes in where clause of the select query.
  
• Indexes also minimize the number of full table scanning for a huge table.
• Tunning on InnoDB buffer pool, MyISAM key cache, and the MySQL query cache helps to cache the results which will be faster retrieval for the repetitive results. We can adjust the size of the cache memory so that it provides faster access by providing results from the cache only.
• Adjust the size and properties of the memory areas that MySQL uses to cache for the InnoDB buffer pool, MyISAM key cache, and the MySQL query cache, which helps run repeated select queries faster.
• We should use WHERE instead of HAVING if we are not using GROUP BY or other aggregate functions like COUNT(), MIN(), MAX(), AVG(), and so on.
• Use EXPLAIN to analyze your query for where clauses, join clauses, and indexes.

Now let's take a look at how EXPLAIN is useful for the optimization of the query performance:

EXPLAIN SELECT * FROM `sales_order_item` i 
INNER JOIN sales_order o ON i.order_id = o.entity_id
INNER JOIN catalog_product_entity p ON p.sku = i.sku
INNER JOIN customer_entity c ON c.entity_id = o.customer_id
WHERE i.order_id = 42 

The following is the output of the query executed:

*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: o
 partitions: NULL
 type: const
possible_keys: PRIMARY,SALES_ORDER_CUSTOMER_ID
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
 id: 1
 select_type: SIMPLE
 table: c
 partitions: NULL
 type: const
possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
*************************** 4. row ***************************
 id: 1
 select_type: SIMPLE
 table: p
 partitions: NULL
 type: ref
possible_keys: CATALOG_PRODUCT_ENTITY_SKU
 key: CATALOG_PRODUCT_ENTITY_SKU
 key_len: 195
 ref: ecommerce.i.sku
 rows: 1
 filtered: 100.00
 Extra: Using index condition
4 rows in set (0.01 sec)

In the preceding output, possible_keys shows which INDEXES apply to this query and KEY tells us which of these was actually used. In our example each JOIN uses the Index key, we should try to avoid the NULL value of the key by creating the index. The ROWS field tells us how many rows are scanned in the query executed to be identified, and should be reduced for better query performance as this would minimize data transfer.