|
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 ;
|
|