Help optimising multiple join fulltext query
Here is the query:
SELECT
action.location, action.CallRef, action.ActBy, action.ActStatus, action.ActText,
call.CustAccCode, call.CallSummary, call.CallText, action.ActSeq,
match(action.ActBy, action.ActText, action.ActStatus, action.CallRef, action.ActSeq) against ('order'),
call.CallLogged, action.ActCompDate
FROM action
INNER JOIN customer ON customer.Location = call.Location
AND call.CustAccCode LIKE customer.CustAccCode
INNER JOIN call ON call.Location = action.Location
AND call.CallRef = action.CallRef
WHERE match(action.ActBy, action.ActText, action.ActStatus, action.CallRef, action.ActSeq) against ('order') > 0.2
AND customer.primeloc = 'RA' and customer.UserName = 'AUSTIN'
AND (action.SecurityLevel IS NULL OR action.SecurityLevel <= 90)
ORDER BY match(action.ActBy, action.ActText, action.ActStatus, action.CallRef, action.ActSeq) against ('order') DESC
LIMIT 0,200
This is infact the optimised query and using MySQL 4.1.10a it returns
200 rows in set (0.11 sec)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: action
type: fulltext
possible_keys: PRIMARY,TEXT
key: TEXT
key_len: 0
ref:
rows: 1
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: call
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 12
ref: calls.action.Location,calls.action.CallRef
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 54
ref: const,const,calls.call.Location
rows: 1
Extra: Using where; Using index
however the production database(s) are MySQL 3.23.33 and 3.23.49 and this query take a very long time indeed (minutes in some cases) to execute. The explain plan for the above query on the production database shows that the text index is not being used:
*************************** 1. row ***************************
table: action
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 299519
Extra: where used; Using filesort
*************************** 2. row ***************************
table: call
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 12
ref: action.Location,action.CallRef
rows: 1
Extra: where used
*************************** 3. row ***************************
table: customer
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 54
ref: const,const,call.Location
rows: 3
Extra: where used; Using index
Sometimes it chooses a different execution plan, it sometimes chooses to scan the action table first, in all cases it chooses to ignore the fulltext index.
I tried using USE INDEX(TEXT) but mysql still chose to ignore it and FORCE INDEX(TEXT) is not supported in version 3.23.
The tables involved are
CREATE TABLE `customer` (
`primeloc` char(2) NOT NULL default '',
`UserName` varchar(50) NOT NULL default '',
`Location` char(2) NOT NULL default '',
`CustAccCode` varchar(50) NOT NULL default '',
`CustName` varchar(50) default NULL,
PRIMARY KEY (`primeloc`,`UserName`,`Location`,`CustAccCode`)
) TYPE=MyISAM
CREATE TABLE `call` (
`Location` char(2) NOT NULL default '',
`CallRef` varchar(10) NOT NULL default '',
`LastUpdated` timestamp(14) NOT NULL,
`SchSerialNo` varchar(50) default NULL,
`ContCode` varchar(50) default NULL,
`CallCustRef` varchar(50) default NULL,
`CallLogged` datetime default NULL,
`CallContact` varchar(50) default NULL,
`CallOwner` varchar(50) default NULL,
`CallSeverity` varchar(50) default NULL,
`CallAnalyst` varchar(50) default NULL,
`CallStatus` varchar(50) default NULL,
`CallTarget` double(32,30) default '0.000000000000000000000000000000',
`CallResponse` double(32,30) default '0.000000000000000000000000000000',
`CallModule` varchar(50) default NULL,
`CallProbArea` varchar(50) default NULL,
`CallText` longtext,
`CallIssueType` varchar(50) default NULL,
`CallTime` int(19) default '0',
`CallSummary` longtext,
`CustAccCode` varchar(50) default NULL,
`CallActChange` datetime default NULL,
`CallBusImp` longtext,
`CallComp` varchar(50) default NULL,
`CallProduct` varchar(50) default NULL,
PRIMARY KEY (`Location`,`CallRef`),
FULLTEXT KEY `TEXT` (`CallContact`,`CallOwner`,`CallAnalyst`,`CallSummary`,`Ca
llText`,`CallIssueType`,`CallCustRef`)
) TYPE=MyISAM
CREATE TABLE `action` (
`Location` char(2) NOT NULL default '',
`CallRef` varchar(50) NOT NULL default '',
`ActSeq` varchar(50) NOT NULL default '0',
`LastUpdated` timestamp(14) NOT NULL,
`ActTime` int(11) default NULL,
`ActCompDate` datetime default NULL,
`ActCompTime` datetime default NULL,
`ActBy` varchar(50) default NULL,
`ActAnalyst` varchar(50) default NULL,
`ActText` longtext,
`ActStatus` varchar(50) default NULL,
`ActType` varchar(50) default NULL,
`ActSubAction` varchar(50) default NULL,
`ActActionType` varchar(50) default NULL,
`ActActivity` varchar(50) default NULL,
`SecurityLevel` int(2) default NULL,
`ActPrivate` char(1) default NULL,
PRIMARY KEY (`Location`,`CallRef`,`ActSeq`),
FULLTEXT KEY `TEXT` (`ActBy`,`ActText`,`ActStatus`,`CallRef`,`ActSeq`)
) TYPE=MyISAM
Is there any way I can further optimize this query to perform well both under 4.1 and 3.23 versions of MySQL ?
Thanks in advance