CODEWITHSUNDEEP
mysqlsql-execution-plan
Michael Barker
Michael Barker

Reputation: 14378

How do I explain a query with parameters in MySQL

I have a query

SELECT foo FROM bar WHERE some_column = ?

Can I get a explain plan from MySQL without filling in a value for the parameter?

Upvotes: 9

Views: 2161

Answers (3)

ircmaxell
ircmaxell

Reputation: 165191

So long as you're doing only an equals (and not a like, which can have short circuit affects), simply replace it with a value:

EXPLAIN SELECT foo FROM bar WHERE some_column = 'foo';

Since it's not actually executing the query, the results shouldn't differ from the actual. There are some cases where this isn't true (I mentioned LIKE already). Here's an example of the different cases of LIKE:

SELECT * FROM a WHERE a.foo LIKE ?
  1. Param 1 == Foo - Can use an index scan if an index exists.
  2. Param 1 == %Foo - Requires a full table scan, even if an index exists
  3. Param 1 == Foo% - May use an index scan, depending on the cardinality of the index and other factors

If you're joining, and the where clause yields to an impossible combination (and hence it will short circuit). For example:

SELECT * FROM a JOIN b ON a.id = b.id WHERE a.id = ? AND b.id = ?

If the first and second parameters are the same, it has one execution plan, and if they are different, it will short circuit (and return 0 rows without hitting any data)...

There are others, but those are all I can think of off the top of my head right now...

Upvotes: 7

a1ex07
a1ex07

Reputation: 37354

I don't think it's possible. WHERE some_column ='value', WHERE some_column = other_column and WHERE some_column = (SELECT .. FROM a JOIN b JOIN c ... WHERE ... ORDER BY ... LIMIT 1 ) return different execution plans.

Upvotes: 1

bwawok
bwawok

Reputation: 15347

The explain plan may be different depending on what you put in. I think explain plans without real parameter don't mean anything.

Upvotes: 2

Related Questions