Re: NAME_CONST slow down the query

[Larry Ng]

mysql> SHOW CREATE PROCEDURE udi_data.CkdSpn\G;
*************************** 1. row ***************************
Procedure: CkdSpn
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
Create Procedure: CREATE DEFINER=`ronc`@`10.128.%` PROCEDURE `CkdSpn`(InMemR
cn DECIMAL(11))
BEGIN
DECLARE Cnt INT DEFAULT 0;
DECLARE Cnt2 INT DEFAULT 0;
DECLARE MemRcn DECIMAL(11) DEFAULT 0;
DECLARE SpnRcn DECIMAL(11) DEFAULT 0;
SELECT COUNT(*) INTO Cnt FROM tblimx_tree_ref WHERE MemberID = InMemRcn;

IF
Cnt = 0
THEN
SELECT DSM_DST_RCN, DSM_SPN_RCN INTO MemRcn, SpnRcn FROM v_dsmfi
le WHERE DSM_DST_RCN = InMemRcn;
SELECT COUNT(*) INTO Cnt2 FROM tblimx_tree_ref WHERE SponsorID =
SpnRcn;
/*
If there are other mebers with the same Sponsor,create a child
else create a new node under the Sponsor
*/
IF
Cnt2 > 0
THEN
CALL CreateChild (MemRcn, SpnRcn);
ELSE
CALL CreateNode (MemRcn, SpnRcn);
END IF;
END IF;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.05 sec)