Re: What is meaning of "func" in "ref" column of EXPLAIN plan
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