full text performance tuning
Table Structure:-
CREATE TABLE `rcdb_005278`.`product_master` (
`productid` int(11) DEFAULT NULL COMMENT 'Originated from eTilize Data',
`manufacturerid` int(11) DEFAULT NULL,
`manufacturername` varchar(80) DEFAULT NULL,
`manufacturerpart` varchar(80) DEFAULT NULL,
`categoryid` int(11) DEFAULT NULL,
`categoryname` varchar(80) DEFAULT NULL,
`subcategoryid` int(11) DEFAULT NULL,
`subcategoryname` varchar(80) DEFAULT NULL,
`title` varchar(1000) DEFAULT NULL,
`description` mediumtext,
`keywords` longtext,
`isextended` tinyint(1) DEFAULT '0' COMMENT 'Equivalent to VARPART',
`isderived` tinyint(1) DEFAULT NULL COMMENT 'Equivalent to VARPART overriding PART',
`isassembly` tinyint(1) DEFAULT NULL COMMENT 'Bundle, Configured Item',
`dimwidth` float DEFAULT NULL,
`dimheight` float DEFAULT NULL,
`dimlength` float DEFAULT NULL,
`dimweight` float DEFAULT NULL,
`uom` varchar(10) DEFAULT NULL,
`minorderqty` float DEFAULT NULL,
`upc` varchar(20) DEFAULT NULL,
`unspsc` varchar(20) DEFAULT NULL,
`msrp` double DEFAULT NULL,
`thumbnail` varchar(255) DEFAULT NULL,
`large` varchar(255) DEFAULT NULL,
`bottom` varchar(255) DEFAULT NULL,
`top` varchar(255) DEFAULT NULL,
`rear` varchar(255) DEFAULT NULL,
`right` varchar(255) DEFAULT NULL,
`left` varchar(255) DEFAULT NULL,
`front` varchar(255) DEFAULT NULL,
`distributorlist` varchar(50) DEFAULT NULL,
`createdon` datetime DEFAULT NULL,
`lastmodifiedon` datetime DEFAULT NULL,
`ID` int(11) NOT NULL AUTO_INCREMENT,
`partid` int(11) DEFAULT NULL,
`PriceUpdated` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ind_productid` (`productid`) USING BTREE,
KEY `i_mfrid` (`manufacturerid`),
KEY `i_catsubcat` (`categoryid`,`subcategoryid`),
KEY `i_subcat` (`subcategoryid`),
KEY `i_mfrpart` (`manufacturerpart`),
KEY `i_mfrname` (`manufacturername`),
KEY `inx_isextended` (`isextended`),
KEY `i_prtid_isext` (`partid`,`isextended`) USING BTREE,
FULLTEXT KEY `ft_keywords` (`keywords`,`categoryname`,`subcategoryname`)
) ENGINE=MyISAM AUTO_INCREMENT=2101944 DEFAULT CHARSET=latin1 COMMENT='Master product data';
Query:-
select pm.`id` ,pm.`productid`,pm.`partid`, pm.`manufacturerid`, pm.`categoryid`,pm.`subcategoryid`,pm.`manufacturername`,pm.`manufacturerpart`
,pm.`categoryname`,pm.`subcategoryname`,pm.`thumbnail`,pm.`large`,pm.`bottom`,pm.`top`,pm.`rear`,pm.`right`,pm.`left`,pm.`front`,pm.`title`,pm.`description`,pm.`isextended`,pm.`isderived`,pm.`isassembly`,pm.`dimwidth`,pm.`dimheight`,pm.`dimlength`,pm.`dimweight`,pm.`uom`,pm.`minorderqty`,pm.`upc`,pm.`unspsc`,ROUND(if(pr.`msrp`,0,pm.msrp),2) as msrp,distributorlist ,pr.`appliedruleid`, ROUND(pr.`cost`,2) as cost,pr.`qtyonhand`, pr.costsourceid , pr.sku , ROUND(pr.`price`,2) as price ,
ROUND(pd.`price`,2) as dprice ,
(MATCH(pm.keywords,pm.categoryname,pm.subcategoryname) AGAINST ('hard derive')) rel
from `rcdb_005278`.`product_master` pm INNER JOIN `rcdb_005278`.`price_08049` pr ON pm.partid = pr.partid AND pm.isextended = pr.isextended INNER JOIN `rcdb_005278`.`price_07909` pd ON pm.partid = pd.partid
AND pm.isextended = pd.isextended
inner join `rcdb_005278`.`category_001409` cat
on cat.id = pm.subcategoryid
WHERE (MATCH(pm.keywords,pm.categoryname,pm.subcategoryname) AGAINST ('hard derive' in boolean mode))
and pr.price != 0 and pm.partid != 0
order by rel desc LIMIT 0, 25
version:- mysql 5.1
ft_min_word_len=2
using default stopword
we are facing performance issue please suggest ......
we dont want to revert back ft_min_word_len=4 or 3
regards
samir