Details
Major
Description
Follow query works very slow because can't use PRIMARY KEY because I use stored function in where. Function is deterministic.
EXPLAIN SELECT id_client, secondname, firstname, middlename, gender, birthdate, promotion FROM crm_client JOIN crm_client_private USING (id_client) WHERE id_client = get_id_client_by_contact(empty2null('79374995945'), empty2null(''))
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ------------------ ------ ------------- ------- ------- -------------------------------- ------ -------------
1 SIMPLE crm_client_private ALL PRIMARY (NULL) (NULL) (NULL) 3167 USING WHERE
1 SIMPLE crm_client eq_ref PRIMARY PRIMARY 4 BPL.crm_client_private.id_client 1 USING WHERE
If divide query on two queries all works as expected.
EXPLAIN SELECT get_id_client_by_contact(empty2null('79374995945'), empty2null(''))
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ ----------------
1 SIMPLE (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) NO TABLES used
SELECT get_id_client_by_contact(empty2null('79374995945'), empty2null(''))
get_id_client_by_contact(empty2null('79374995945'), empty2null(''))
---------------------------------------------------------------------
215798
EXPLAIN SELECT id_client, secondname, firstname, middlename, gender, birthdate, promotion FROM crm_client JOIN crm_client_private USING (id_client) WHERE id_client = 215798
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ------------------ ------ ------------- ------- ------- ------ ------ --------
1 SIMPLE crm_client const PRIMARY PRIMARY 4 const 1
1 SIMPLE crm_client_private const PRIMARY PRIMARY 4 const 1
CREATE DEFINER=`bpl`@`%` FUNCTION `get_id_client_by_contact`( par_phone VARCHAR(100) , par_email VARCHAR(100) ) RETURNS INT(10) UNSIGNED DETERMINISTIC -- READS SQL DATA BEGIN DECLARE var_id_client INT(10) UNSIGNED; -- search by phone SELECT id_client INTO var_id_client FROM crm_client_contact JOIN crm_contact_type USING (id_contact_type) WHERE short = 'mobile' AND par_phone = contact; -- if not found search by email IF var_id_client IS NULL THEN SELECT id_client INTO var_id_client FROM crm_client_contact JOIN crm_contact_type USING (id_contact_type) WHERE short = 'email' AND par_email = contact; END IF; RETURN var_id_client; END$$ DELIMITER ;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Closing as requested.
Sorry, empty2null function was non deterministic.
I am added DETERMINISTIC key word into empty2null function and problem was disappeared.
Please, close this issue.