MySQL Forums
Forum List  »  Full-Text Search

full text performance tuning
Posted by: samir devaliya
Date: May 12, 2012 04:20AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
full text performance tuning
4078
May 12, 2012 04:20AM
2275
May 14, 2012 10:23PM


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.