How to Use Explain Plan to Optimize Queries

How to use Explain Plan to optimize queries?

You get more than that actually depending on what you are doing. Check out this explain plan page. I'm assuming a little bit here that you are using Oracle and know how to run the script to display the plan output. What may be more important to start with is looking at the left hand side for the use of a particular index or not and how that index is being utilized. You should see things like "(Full)", "(By Index Rowid)", etc if you are doing joins. The cost would be the next thing to look at with lower costs being better and you will notice that if you are doing a join that is not using an index you may get a very large cost. You may also want to read details about the explain plan columns.

How to optimise MySQL queries based on EXPLAIN plan

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.

Explain plan for long running query in oracle sql developer

As you are using a mix of local tables and remote tables. If the tables on the remote database are larger than the ones on the local database then you might need to use the DRIVING_SITE hint so the smaller of the set of tables are moved to the database issuing the call.

DRIVING_SITE

SQL query optimization taking lot of time in execution

Try adding this compound index:

ALTER TABLE tbl_property_meta ADD INDEX id_key (property_id, meta_key);

If it doesn't help make things faster, try this one.

ALTER TABLE tbl_property_meta ADD INDEX key_id (meta_key, property_id);

And, you should know that column LIKE '%somevalue' (with a leading %) is a notorious performance antipattern, resistant to optimization via indexes. (There's a way to create indexes for that shape of filter in PostgreSQL, but not in MariaDB / MySQL.)

---

Related Topics