Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7897

MariaDB can't use index when I use result of FUNCTION in WHERE query

    XMLWordPrintable

    Details

      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 ;

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            mikhail Mikhail Gavrilov
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: