Tengo 10 tablas con la misma estructura, excepto el nombre de la tabla.

Tengo un SP (procedimiento almacenado) definido de la siguiente manera:

 select * from table1 where (@param1 IS NULL OR col1=@param1)
 UNION ALL
 select * from table2 where (@param1 IS NULL OR col1=@param1)
 UNION ALL
 ...
 ...
 UNION ALL
 select * from table10 where (@param1 IS NULL OR col1=@param1)

Estoy llamando a la sp con la siguiente línea:

call mySP('test')  //it executes in 6,836s

Entonces abrí una nueva ventana de consulta estándar. Acabo de copiar la consulta de arriba. Luego reemplazó @ param1 con 'test'.

Esto se ejecutó en 0,321s y es aproximadamente 20 veces más rápido que el procedimiento almacenado.

Cambié el valor del parámetro repetidamente para evitar que el resultado se almacene en caché. Pero esto no cambió el resultado. El SP es aproximadamente 20 veces más lento que la consulta estándar equivalente.

Por favor, ¿puedes ayudarme a averiguar por qué sucede esto?

¿Alguien encontró problemas similares?

Estoy utilizando mySQL 5.0.51 en Windows Server 2008 R2 64 bit.

Edición: Estoy usando Navicat para la prueba.

Cualquier idea me será de ayuda.

EDIT1:

Acabo de hacer algunas pruebas según la respuesta de Barmar.

Finalmente, he cambiado el sp como abajo con una sola fila:

 SELECT * FROM table1 WHERE col1=@param1 AND col2=@param2

Luego, en primer lugar, ejecuté la consulta estándar

 SELECT * FROM table1 WHERE col1='test' AND col2='test'  //Executed in 0.020s

Después de que llamé el my sp:

 CALL MySp('test','test')    //Executed in 0.466s

Así que he cambiado por completo la cláusula pero nada cambió. Y llamé a sp desde la ventana de comandos de mysql en lugar de a navicat. Dio el mismo resultado. Todavía estoy atrapado en él.

mi ddl sp

 CREATE DEFINER = `myDbName`@`%`
 PROCEDURE `MySP` (param1 VARCHAR(100), param2 VARCHAR(100))
 BEGIN
    SELECT * FROM table1 WHERE col1=param1 AND col2=param2
 END

Y col1 y col2 se combinan indexados.

Se podría decir que, ¿por qué no se usa la consulta estándar entonces? El diseño de mi software no es apropiado para esto. Debo utilizar el procedimiento almacenado. Así que este problema es muy importante para mí.

EDIT2:

He recibido información de perfil de consulta. La gran diferencia se debe al "envío de la fila de datos" en la información de perfil de SP. Enviar parte de datos toma% 99 del tiempo de ejecución de la consulta. Estoy haciendo la prueba en el servidor de base de datos local. No estoy conectando desde una computadora remota.

Información de perfil de SP

Información de perfil de consulta

He intentado forzar declaración de índice como abajo en mi sp. Pero mismo resultado.

 SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=@param1 AND col2=@param2

He cambiado sp como abajo.

 EXPLAIN SELECT * FROM table1 FORCE INDEX (col1_col2_combined_index) WHERE col1=param1 AND col2=param2

Esto dio este resultado:

 id:1
 select_type=SIMPLE
 table:table1
 type=ref
 possible_keys:NULL
 key:NULL
 key_len:NULL
 ref:NULL
 rows:292004
 Extra:Using where

Entonces he ejecutado la siguiente consulta.

 EXPLAIN SELECT * FROM table1 WHERE col1='test' AND col2='test'

El resultado es:

 id:1
 select_type=SIMPLE
 table:table1
 type=ref
 possible_keys:col1_co2_combined_index
 key:col1_co2_combined_index
 key_len:76
 ref:const,const
 rows:292004
 Extra:Using where

Estoy utilizando la instrucción FORCE INDEX en SP. Pero insiste en no usar el índice. ¿Alguna idea? Creo que estoy cerca de terminar :)