MySQL Forums
Forum List  »  Optimizer & Parser

Re: What is meaning of "func" in "ref" column of EXPLAIN plan
Posted by: Jørgen Løland
Date: August 08, 2011 05:01AM

Hi Leo,

I hadn't noticed this before so I had to dig a little to find out what was happening. EXPLAIN EXTENDED gave a hint:

EXPLAIN EXTENDED
(...)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	customers	ALL	NULL	NULL	NULL	NULL	1	100.00	
1	SIMPLE	AccountsReceivable	ref	customerId,datetype_idx	customerId	195	func	1	100.00	Using index condition; Using where

Warnings:
Note	1003	select `test`.`AccountsReceivable`.`createUser` AS
`createUser`,`test`.`AccountsReceivable`.`created` AS 
`created`,`test`.`customers`.`CustomerID` AS 
`CustomerID`,`test`.`AccountsReceivable`.`type` AS 
`type`,`test`.`AccountsReceivable`.`amount` AS 
`amount`,`test`.`AccountsReceivable`.`invoiceNumber` AS 
`invoiceNumber`,`test`.`customers`.`Phone` AS `Phone` from 
`test`.`AccountsReceivable` join `test`.`customers` where 
((`test`.`AccountsReceivable`.`createUser` <> '') and 
(`test`.`AccountsReceivable`.`created` between '20071004' and '20071011') and 
(`test`.`AccountsReceivable`.`type` = 'CHARGE') and 
(`test`.`AccountsReceivable`.`customerId` = 
convert(`test`.`customers`.`CustomerID` using utf8)))     <<-- NOTICE THIS

If I change character set to UTF8 in both tables, I get this execution plan:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	customers	ALL	CustomerID_idx	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	AccountsReceivable	ref	customerId,datetype_idx	customerId	195	test.customers.CustomerID	1	100.00	Using where

So "func" means that an index lookup is done using customer.CustomerID values that go through conversion from latin1 to UTF8.

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: What is meaning of "func" in "ref" column of EXPLAIN plan
2867
August 08, 2011 05:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.