Re: When does Stored Procedure execution plan get determined?

[Robert Rothkopf]

To shed a bit more context on the "why is he asking"....

If the SP is compiled and execution plans along with it, and then re-used, it could produce some seriously suboptimal query plans if the queries are dependent on parameters passed into the SP.

Consider a "search" SP which accepts different search options and criteria via parameters, some being NULL, and non-NULL parameters being used as part of the WHERE clause.


- TABLE MYTABLE has columns name, city, and others
- Indexes exist on both MYTABLE.name and MYTABLE.city
- The SP has PARAMETERS -- cNamesearch CHAR(60) and cCity CHAR(60)

A query within the SP exists, like this:

select * from MYTABLE
where
( ( (cNamesearch is NULL) or (MYTABLE.name like cNamesearch))
and ( (cCity is NULL) or (MYTABLE.city like cCity))
)

The execution plan should be very different depending on the values of cNamesearch and cCity, using one index or another.

SQL Server allows us to specify "WITH RECOMPILE" to force new query plans to be figured out based on parameters; nothing similar exists for MySQL, and it's not evident from docs or online reading how this is handled.

Will new execution plans be considered every single time the SP is called, or only once when it is compiled and/or put into Cache?