How will you optimize a SELECT query in MySQL?

We can use following options to increase the speed of a SELECT query in MySQL:

  • Index: We can create index on the columns that are used in WHERE clause of SELECT statement.
  • Function Isolation: We can check whether a function is being called multiple times or one time. By isolating the specific function that takes longer time in a query, we can optimize it to improve the overall performance of a query.
  • Full Table Scan: Wherever possible we should minimize the full table scan in a query. We can use optimal WHERE clause to do this.
  • Table Statistics: MySQL has an in-built optimizer that can optimize a sql query. But it needs current statistics from the table. To keep the statistics up to date we can run ANALYZE statement on a table.
  • Memory: We can also adjust the size and properties of Memory area to optimize the execution of a query in MySQL.
Read the full book at www.amazon.com
Posted in MySQL, MySQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*