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.