Bug #64113 Indexes not used when User Defined Function specified in WHERE

• Submitted: 23 Jan 2012 19:07
• Modified: 23 Jan 2012 19:15
• Reporter: Van Stokes
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 5.5.19 x64
• OS: Linux (Ubuntu 11.10 x86_64)
• CPU Architecture: Any
• Tags: FUNCTION, INDEX, where

[23 Jan 2012 19:07] Van Stokes

Description:
When using a user defined function in a WHERE clause, MySQL performs a full table scan even when FORCE INDEX is specified.

How to repeat:
The following performs a full table scan:

SELECT * FROM lcl_relay_plan FORCE INDEX( lcl_relay_plan_uidx1 )
WHERE poo_id = UnLocationGetIDByCode( "USSEA" )
AND fd_id = UnLocationGetIDByCode( "JMKIN" )
ORDER BY seg_no;

The following properly uses index (even without the FORCE INDEX):

SELECT * FROM lcl_relay_plan FORCE INDEX( lcl_relay_plan_uidx1 )
WHERE poo_id = 51698
AND fd_id = 30715
ORDER BY seg_no;

Suggested fix:
Examine the result type from the User Defined function.

[23 Jan 2012 19:07] Van Stokes

Tables and Function syntax

Attachment: bug.sql (text/x-sql), 4.59 KiB.

[23 Jan 2012 19:08] Van Stokes

Changed Severity

[23 Jan 2012 19:10] Van Stokes

There is the user defined function:

CREATE FUNCTION `UnLocationGetIDByCode`( pLocCode VARCHAR(10) ) RETURNS INT(11)
READS SQL DATA
MAIN: BEGIN

	DECLARE mKey INT(11) DEFAULT NULL;

	IF ISNULL( pLocCode ) = FALSE AND TRIM( pLocCode ) <> "" THEN
		SELECT id INTO mKey FROM un_location WHERE un_loc_code = pLocCode LIMIT 1;
	END IF;

	RETURN mKey;

END MAIN $$

[23 Jan 2012 19:14] Van Stokes

My bad. I forgot to include "DETERMINISTIC" in the functions declaration.

[23 Jan 2012 19:14] Valeriy Kravchuk

Had you tried to add DETERMINISTIC clause to function definition?

[23 Jan 2012 19:15] Valeriy Kravchuk

You were faster...