How to optimise MySQL queries based on EXPLAIN plan

by

Depends on what you’re going for and what the query is.

Generally, for every line in EXPLAIN that has a Using where, you need to have it using an index (possible keys and keys column). These are your filters and include WHERE and ON. Having it say Using index is even better. It means there’s a covering index, and MySQL can retrieve the data right from the index rather than visiting the row in the table data.

The lines where there is no Using where, and it is returning a large number of rows should be looked at. These are returning values for all rows in the table. I don’t know what your query is, so I don’t know whether to be alarmed here. Try filtering the result set to reduce the size and improve performance.

You generally should try to avoid seeing Using filesort or Using temporary, though those are only bad if you’re not expecting them.

Filesort usually appears with the ORDER clause. You generally want MySQL to use a covering index (Using index) so that the rows are returned already in order from the server. If they’re not, then MySQL must order them afterward, using filesort.

Using temporary can be bad when it refers to derived tables because they don’t have indexes. It seems that you’ve explicitly created a temporary table with indexes, so here, it’s not bad. Sometimes, your only choice is to use a derived table, and hence Using temporary.

More Related Contents:

  1. How to do the Recursive SELECT query in MySQL?
  2. JOIN queries vs multiple queries
  3. MySQL indexes – what are the best practices?
  4. Subqueries with EXISTS vs IN – MySQL
  5. mysql select from n last rows
  6. Difference in MySQL JOIN vs LEFT JOIN
  7. 60 million entries, select entries from a certain month. How to optimize database?
  8. Checking multiple columns for one value
  9. Mysql Improve Search Performance with wildcards (%%)
  10. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  11. mysql, ifnull vs coalesce, which is faster?
  12. Higher cardinality column first in an index when involving a range?
  13. MySQL “IN” queries terribly slow with subquery but fast with explicit values
  14. MySQL: Can I do a left join and pull only one row from the join table?
  15. How to estimate SQL query timing?
  16. Deleting millions of rows in MySQL
  17. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  18. How to split the name string in mysql?
  19. Is there a REAL performance difference between INT and VARCHAR primary keys?
  20. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  21. Can’t Create Entity Data Model – using MySql and EF6
  22. In MySQL, can I copy one row to insert into the same table?
  23. How to copy a row and insert in same table with a autoincrement field in MySQL?
  24. MySQL: Cloning a MySQL database on the same MySql instance
  25. MySQL sharding approaches?
  26. Does Order of Fields of Multi-Column Index in MySQL Matter
  27. NodeJS MySQL Dump
  28. Return Last ID (IDENTITY) On Insert row VB.NET MySQL
  29. Linux shell script for database backup
  30. WHERE datetime older than some time (eg. 15 minutes)

Leave a Comment