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