MySQL – How SELECT statements should be optimizedFeb 26, 2011

Web applications do more read (SELECT) operations from database rather than write (INSERT/UPDATE/DELETE) operations. Usually record inserted only once and viewed millions of times, SELECT statements if not written correctly perform lookup in all table, although MySQL query optimizer analyzes all queries to check if any optimizations can be used to process the query more quickly, but it should be developers top priority to write their SELECT statements correctly and minimize the lookup operation. On heavy traffic websites even a fraction of a second matters, following should be considered for optimizing SELECT queries.

  • Set up proper indexes on columns used in the WHERE clause. Indexes are especially very important for JOIN statements.
  • Try to reduce the disk scans especially for large tables. Following queries will be executed very fast because they will not scan whole table.
    SELECT COUNT(*) FROM table; /* Without where clause not require a single table row scan, it will read data from table's metadata */
    SELECT MIN(pk_id) FROM table; /*Aggregate functions like MIN and MAX */
    SELECT pk_id from table ORDER BY pk_id LIMIT 5; /* ORDER BY ASC or DESC with LIMIT*/
    SELECT * FROM table WHERE pk_id = numeric_value; /* Comparison of primary id with numeric value */
  • Avoid using column in a function call.
    1. Avoid using column in a function call as it will be executed for every row, even if you are using an indexed column MySQL can't use the index because it will have to compute the function value for every row, although sometimes this is mandatory but most of the times you can rewrite a query to make indexed columns stand alone.
    2. SELECT * FROM table 
      WHERE SUBSTRING(email, 1, (LOCATE('@', email) - 1)) in ('steve', 'smith'); 
      /* If you have 1 million records in table these functions LOCATE and SUBSTRING will be executed 1 million times each*/
  • Use wildcards carefully in a LIKE pattern.
    1. Try to avoid using wild card altogether if possible. ( I know this is very unlikely :) ).
    2. If mandatory to use wildcard don't put "%" on both sides of the string.
  • Carefully write JOIN statements.
    1. Try to compare same data type columns in JOIN statements.
    2. MySQL optimizer analyzes the order in which rows can be retrieved most quickly. Sometimes it makes a non-optimal choice; to avoid this happening you can override the optimizer's choice using the STRAIGHT_JOIN keyword in JOIN statements. Have a run of EXPLAIN to see the query both with and without STRAIGHT_JOIN.
  • Prefer JOIN over sub-query - MySQL optimizer has been better tuned for JOINs than for sub queries in some cases, so it is always better to write JOIN query which is logically equivalent of a sub query you have and check both of them with EXPLAIN statement.
  • Use EXPLAIN statement more often - The EXPLAIN statement tells you whether indexes are being used or not.

blog comments powered by Disqus
Me Hi! My name is Zeeshan Muhammad Khan (nick name Shan) and I am a software engineer, database developer, web developer, programming geek, statistics geek, mathematics geek, system analyst and maintainer of this site. read more

Web Shelf