Bug #34254 Very slow queries when using functions in SELECT statements
Submitted: 2 Feb 2008 20:26 Modified: 4 Feb 2008 2:23
Reporter: Van Stokes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1.22 RC (x64) OS:Microsoft Windows (XP x64 AMD DC)
Assigned to: CPU Architecture:Any
Tags: innodb, SELECT

[2 Feb 2008 20:26] Van Stokes
Description:
When using a FUNCTION to complete part of a WHERE clause there is a huge performance problem. Scenario: We have an inventory database. Inventory items can be "contained" with in another inventory item (i.e. kitting). There are times that we need to find what the highest level inventory item is from some child point. Example: Inventory item A1 contains B1 which contains C1 whcih contains D1. A1's container (ContInvKey) will be zero (highest level). A row/col view would look like this:

A1, 0    (highest level)
B1, A1
C1, B1
D1, C1

"Inventory" Database table:

CREATE TABLE Inventory
  (
    InventoryKey     int             unsigned default 0 not null,
    ContInvKey       int             unsigned default 0 not null,
    InventoryName    VARCHAR(128)    ,

   primary key(InventoryKey),

  )
 TYPE = InnoDB;

CREATE INDEX Inventory_IDX1 ON Inventory(ContInvKey);

We wrote this SQL function:

CREATE FUNCTION InventoryGetHighestInvKey( pInventoryKey INT UNSIGNED ) RETURNS INT UNSIGNED
MAIN: BEGIN

	DECLARE mInventoryKey INT UNSIGNED DEFAULT 0;
	DECLARE mLastInventoryKey INT UNSIGNED DEFAULT 0;

	SET mLastInventoryKey = pInventoryKey;

	SEARCH: LOOP
		
		-- Get the Parent/Containing Inventory Key.
		SET mInventoryKey = InventoryGetParentInvKey( mLastInventoryKey );
		
		IF mInventoryKey < 1 THEN
			-- This inventory key is not contained.
			-- That is, it's the HIGHEST most item in the hiarchy.
			LEAVE SEARCH;
		END IF;

		SET mLastInventoryKey = mInventoryKey;

	END LOOP SEARCH;

	RETURN mLastInventoryKey;

END MAIN;

and then there is this function:

CREATE FUNCTION InventoryGetParentInvKey( pInventoryKey INT UNSIGNED ) RETURNS INT UNSIGNED
MAIN: BEGIN

	DECLARE mInventoryKey INT UNSIGNED DEFAULT 0;

	IF pInventoryKey > 0 THEN
		-- Get the Parent/Containing Inventory Key.
		SELECT ContInvKey
			INTO mInventoryKey
			FROM Inventory
			WHERE InventoryKey = pInventoryKey;
	END IF;

	RETURN mInventoryKey;

END MAIN;

How to repeat:
If you execute the FUNCTION like this:

SELECT InventoryGetHighestInvKey( 123456 );

it will return a key within milliseconds (database has ~1M records).

This statement is also instantaneous:

SELECT * FROM Inventory WHERE InventoryKey = 9234733;

However, execute a query like this:

SELECT * FROM Inventory WHERE InventoryKey = InventoryGetHighestInvKey( 123456 );

and the query takes minutes to run!

We have investigated other tables and they behave the same when we use a function to complete a WHERE clause. We use functions heavily to get primary keys by name (name being a varchar) and they all do the same thing. Example:

SELECT * FROM OrderLineItems WHERE InventoryKey = InventoryGetKeyByName( "Shovel" );

Suggested fix:
I don't know. When watching the process via the SQL Administrator Server Connections, it appears that it keeps RE-Executing the functions over and over and over again for each row. If that's true, then that would explain the problem.
[3 Feb 2008 19:25] Valeriy Kravchuk
Thank you for a detailed problem report. Please, try to declare function as DETERMINISTIC and check if you'll get the results faster.
[4 Feb 2008 2:23] Van Stokes
Yes that did it! Declaring the function with the DETERMINISTIC option made all the difference in the world. Example:

CREATE FUNCTION InventoryGetLocationInvKey( pInventoryKey INT UNSIGNED ) RETURNS INT UNSIGNED
 DETERMINISTIC
MAIN: BEGIN

	DECLARE mInventoryKey INT UNSIGNED DEFAULT 0;
	DECLARE mLastInventoryKey INT UNSIGNED DEFAULT 0;

	SET mLastInventoryKey = pInventoryKey;

	SEARCH: LOOP
		
		-- Get the Parent/Containing Inventory Key.
		SET mInventoryKey = InventoryGetParentInvKey( mLastInventoryKey );
		
		IF mInventoryKey < 1 THEN
			-- This inventory key is not contained and therefore
			-- is an inventory item that appears in a scene.
			-- That is, it's the HIGHEST most item in the hiarchy.
			LEAVE SEARCH;
		END IF;

		SET mLastInventoryKey = mInventoryKey;

	END LOOP SEARCH;

	RETURN mLastInventoryKey;

END MAIN;