MySQL Forums
Forum List  »  Optimizer & Parser

Re: What is meaning of "func" in "ref" column of EXPLAIN plan
Posted by: Leo Hopkins
Date: August 05, 2011 06:12AM

mysql> show create table customers \G
*************************** 1. row ***************************
Table: customers
Create Table: CREATE TABLE `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`CustomerID` varchar(64) DEFAULT NULL,
`username` varchar(128) DEFAULT NULL,
`password` varchar(64) DEFAULT NULL,
`FirstName` varchar(128) DEFAULT NULL,
`LastName` varchar(128) DEFAULT NULL,
`gender` char(1) NOT NULL DEFAULT 'U',
`Company` varchar(128) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`Address2` varchar(128) DEFAULT NULL,
`City` varchar(128) DEFAULT NULL,
`State` varchar(128) DEFAULT NULL,
`Zip` varchar(32) DEFAULT NULL,
`Country` varchar(128) DEFAULT NULL,
`billAddressVerified` char(1) NOT NULL DEFAULT 'U',
`Phone` varchar(32) DEFAULT NULL,
`Fax` varchar(32) DEFAULT NULL,
`Email` varchar(128) DEFAULT NULL,
`Ship_FirstName` varchar(128) DEFAULT NULL,
`Ship_LastName` varchar(128) DEFAULT NULL,
`Ship_Company` varchar(128) DEFAULT NULL,
`Ship_Phone` varchar(16) DEFAULT NULL,
`Ship_Fax` varchar(16) DEFAULT NULL,
`Ship_Email` varchar(128) DEFAULT NULL,
`Ship_Address` varchar(255) DEFAULT NULL,
`Ship_Address2` varchar(128) DEFAULT NULL,
`Ship_City` varchar(128) DEFAULT NULL,
`Ship_State` varchar(128) DEFAULT NULL,
`Ship_ZIP` varchar(32) DEFAULT NULL,
`Ship_Country` varchar(128) DEFAULT NULL,
`shipAddressVerified` varchar(8) NOT NULL DEFAULT 'U',
`shipAddressNotes` text NOT NULL,
`notes` text,
`affiliate` varchar(32) DEFAULT NULL,
`sub_affiliate` varchar(255) DEFAULT NULL,
`companies_id` int(11) NOT NULL DEFAULT '0',
`partnerUNID` varchar(255) DEFAULT NULL,
`partner_name` varchar(64) DEFAULT NULL,
`website` varchar(128) DEFAULT NULL,
`editdate` varchar(8) DEFAULT NULL,
`edittime` varchar(8) DEFAULT NULL,
`edituser` varchar(255) DEFAULT NULL,
`editip` varchar(32) DEFAULT NULL,
`createdate` varchar(8) DEFAULT NULL,
`createtime` varchar(8) DEFAULT NULL,
`createuser` varchar(255) DEFAULT NULL,
`createip` varchar(32) DEFAULT NULL,
`myob_import_batch` varchar(128) DEFAULT NULL,
`myob_card_id` varchar(64) DEFAULT NULL,
`myob_FirstName` varchar(64) DEFAULT NULL,
`myob_LastName` varchar(128) DEFAULT NULL,
`is_proper_dup` varchar(8) NOT NULL DEFAULT 'N',
`seo_engine` varchar(255) DEFAULT NULL,
`seo_keyword` varchar(255) DEFAULT NULL,
`seo_landingpage` varchar(255) DEFAULT NULL,
`hasAuto` varchar(8) DEFAULT NULL,
`wholesale` char(2) NOT NULL DEFAULT 'N',
`JournalMemo` longtext NOT NULL,
`isDC` int(2) NOT NULL DEFAULT '0',
`SDI` char(2) DEFAULT NULL,
`DNR` tinyint(1) NOT NULL DEFAULT '0',
`DNM` tinyint(1) NOT NULL DEFAULT '0',
`DNC` tinyint(1) NOT NULL DEFAULT '0',
`DNE` tinyint(1) NOT NULL DEFAULT '0',
`accountStatus` varchar(16) DEFAULT NULL,
`channelMapId` int(4) NOT NULL DEFAULT '0',
`callerid_phone` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `CustomerID_idx` (`CustomerID`),
KEY `Email_idx` (`Email`),
KEY `Zip_idx` (`Zip`),
KEY `phone_idx` (`Phone`(10)),
KEY `callerid_idx` (`callerid_phone`),
KEY `name_idx` (`LastName`(24),`FirstName`(8)),
KEY `ship_name_idx` (`Ship_LastName`(24),`Ship_FirstName`(8)),
KEY `ship_zip_idx` (`Ship_ZIP`),
KEY `shipAddressVerified_idx` (`shipAddressVerified`),
KEY `billAddressVerified_idx` (`billAddressVerified`),
KEY `createdate_idx` (`createdate`),
KEY `username_idx` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=5128090 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)




mysql> show create table AccountsReceivable \G
*************************** 1. row ***************************
Table: AccountsReceivable
Create Table: CREATE TABLE `AccountsReceivable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`customerId` varchar(64) DEFAULT NULL,
`transactionId` varchar(64) DEFAULT NULL,
`invoiceNumber` varchar(64) DEFAULT NULL,
`amount` double DEFAULT '0',
`type` varchar(10) DEFAULT NULL,
`subType` int(11) DEFAULT NULL,
`reasonCode` int(11) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`createUser` varchar(128) DEFAULT NULL,
`comments` blob,
PRIMARY KEY (`id`),
KEY `customerId` (`customerId`),
KEY `transactionId` (`transactionId`),
KEY `invoiceNumber` (`invoiceNumber`),
KEY `datetype_idx` (`created`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=22924616 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


The original query is

EXPLAIN
SELECT AccountsReceivable.createUser,
AccountsReceivable.created,
customers.CustomerID,
AccountsReceivable.type,
AccountsReceivable.amount,
AccountsReceivable.invoiceNumber,
customers.Phone
FROM AccountsReceivable
INNER JOIN
customers
ON (AccountsReceivable.customerId = customers.CustomerID)
WHERE (AccountsReceivable.createUser <> '')
AND (AccountsReceivable.created BETWEEN '20071004' AND '20071011')
AND (AccountsReceivable.type ="CHARGE") \G


*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customers
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5173925
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: AccountsReceivable
type: ref
possible_keys: customerId,datetype_idx
key: customerId
key_len: 195
ref: func
rows: 6
Extra: Using where
2 rows in set (0.00 sec)


I dont think there is any column named "func" and this query takes more than 15 mins to execute

Can you please help me out to fasten up the output and also make me understand what does the "func" means


Thanks in advance

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: What is meaning of "func" in "ref" column of EXPLAIN plan
3256
August 05, 2011 06:12AM


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.