MySQL Forums
Forum List  »  Performance

Help optimising multiple join fulltext query
Posted by: Austin France
Date: April 01, 2005 10:44AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Help optimising multiple join fulltext query
3240
April 01, 2005 10:44AM


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.