MySQL Forums
Forum List  »  InnoDB

Mysql Match…against query performance in InnoDB Mysql 5.6
Posted by: stephen dsouza
Date: July 10, 2015 01:51AM

I need to search records in the table which contains millions of records. I have recently updated Mysql version from 5.1 to 5.6.

I was using `like` in query which was taking around 15 sec to 30 sec.

Currently I have modified query to use `MATCH... AGAINST` feature of mysql 5.6 INNODB. My query goes like this.


SELECT JOB.IDJOB, JOB.IDEMPLOYER ..... FROM JOB WHERE ( ( JOB.ITJOBSTARTTYPE=2 AND JOB.DTPLANNEDEND >= '2015-07-06' ) OR ( JOB.ITJOBSTARTTYPE=1 AND ( ( JOB.ITJOBENDTYPE=2 ) OR ( JOB.DTJOBEND>='2015-07-06') ) ) OR ( JOB.ITJOBSTARTTYPE=3 AND ( (JOB.DTJOBEND >='2015-07-06') OR (JOB.ITJOBENDTYPE=2) ) ) ) AND MATCH(VCJOBTITLE, LVJOBCOMPANYDESCRIPTION, VCCOMPANYNAME, VCSALARYDESC, VCCITY, VCQUALIFICATIONREQUIRED,VCJOBREFERENCE, LVJOBKEYWORDS ) AGAINST ('test' IN NATURAL LANGUAGE MODE) ORDER BY JOB.ITJOBBAND ASC,JOB.VCRANKING DESC,JOB.IDJOB DESC,JOB.FJOBWEIGHT ASC LIMIT 50;

Which takes 80- 120 seconds (3X slower )

If I remove `ORDER BY` it will load in 6 to 10 sec.

Is their any setting Or performance tunning we can apply here?



SHOW CREATE TABLE JOB;


CREATE TABLE `JOB` (
`idJob` int(11) NOT NULL AUTO_INCREMENT,
`idEmployer` int(10) DEFAULT NULL,
`vcJobTitle` varchar(255) NOT NULL,
`lvJobCompanyDescription` text,
`idBasket` int(10) DEFAULT NULL,
`dtActualgolive` datetime DEFAULT NULL COMMENT 'whenever job status become online',
`boImmediatelygolive` int(11) NOT NULL,
`dtRequestedgolive` date DEFAULT NULL,
`dtActualend` datetime DEFAULT NULL COMMENT 'when job is set to be archieved',
`dtPlannedend` date NOT NULL COMMENT 'when posting a job this is calculated. batch will look at this date. date on which regular jobs will be archievd, or date on which internship jobs will be changed to listing',
`dtRequestedend` date DEFAULT NULL COMMENT 'exact date on which employer wants jobs to be archieved',
`dtApplicationdeadline` date DEFAULT NULL COMMENT 'job will be converted to listing on this date',
`boDurationinweeks` int(11) NOT NULL COMMENT 'whether employer gave duration in weeks or not',
`itDurationweeks` int(2) DEFAULT NULL,
`boIsoncredit` int(11) NOT NULL,
`dtCreditend` date DEFAULT NULL,
`fJobbaseprice` float NOT NULL,
`fJobpriceafteradminitemdiscount` float NOT NULL COMMENT 'price after admin item discount',
`fJobpriceafterpromodiscount` float NOT NULL COMMENT 'price after promo discount',
`vcPromotioncode` varchar(50) DEFAULT NULL,
`vcCompanyname` varchar(100) NOT NULL,
`vcSalarydesc` varchar(255) NOT NULL,
`vcCity` varchar(100) NOT NULL,
`vcCounty` varchar(120) DEFAULT NULL,
`vcQualificationrequired` text,
`boWorkfromhome` int(11) NOT NULL,
`boResidential` int(11) NOT NULL,
`boIndoor` int(11) NOT NULL,
`boOutdoor` int(11) NOT NULL,
`boIndoorandoutdoor` int(11) NOT NULL,
`itJobstarttype` int(11) DEFAULT NULL COMMENT 'date,immidiate,always recruiting',
`dtJobstart` date DEFAULT NULL,
`itJobendtype` int(11) DEFAULT NULL COMMENT 'date,ongoing',
`dtJobend` date DEFAULT NULL,
`dtFeaturedstart` date DEFAULT NULL,
`dtFeaturedend` date DEFAULT NULL,
`itFulltimeparttime` int(11) NOT NULL COMMENT 'fulltime,parttime,both',
`boEveningtime` int(11) NOT NULL,
`boDaytime` int(11) NOT NULL,
`boWeekend` int(11) NOT NULL,
`boNewsletter` int(11) NOT NULL,
`vcApplyemail` varchar(500) DEFAULT NULL,
`vcApplyphone` varchar(200) DEFAULT NULL,
`vcApplyaddress` varchar(100) DEFAULT NULL,
`vcApplyURL` varchar(500) DEFAULT NULL,
`boRequirephone` int(11) DEFAULT NULL,
`boRequireaddress` int(11) DEFAULT NULL,
`boRequirecv` int(11) DEFAULT NULL,
`boFeatured` int(11) NOT NULL,
`blLogo` longblob,
`itJobstatus` int(1) DEFAULT NULL COMMENT 'online,offline',
`itEmailedtofriendcount` int(5) DEFAULT NULL COMMENT 'keeps count of how many times users clicked on link email this to friend',
`itDeadlinetype` int(1) DEFAULT NULL,
`idInternshiptypemaster` int(11) DEFAULT NULL,
`vcLengthofscheme` varchar(100) DEFAULT NULL,
`dtInternshiplistingend` date DEFAULT NULL COMMENT 'date when internship job will archieve',
`itJobband` int(2) DEFAULT NULL,
`boGraduate` int(11) NOT NULL,
`boInternship` int(11) NOT NULL,
`boGaptemp` int(11) NOT NULL,
`boParttimeholiday` int(11) NOT NULL,
`boEntrylevel` int(11) NOT NULL DEFAULT '0',
`boHundredPercentDiscountApplicable` int(11) NOT NULL,
`vcContactdetails` varchar(100) DEFAULT NULL,
`vcJobreference` varchar(100) DEFAULT NULL COMMENT 'for importing totaljobs feeds',
`vcJoburlparam` varchar(100) DEFAULT NULL COMMENT 'for importing totaljobs url params',
`dtPutInCurrentBand` datetime DEFAULT NULL COMMENT 'date when job is online and put in band 3',
`itInitcountycount` int(2) NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`lvJobkeywords` text,
`fJobweight` decimal(10,2) DEFAULT '1.00' COMMENT 'Holds the job weight ranging between 0 to 1 (1 being the highest weight)',
`dtReinstatedOn` datetime DEFAULT NULL,
`boIncludeiniframe` int(11) DEFAULT NULL,
`boIsAdvertFeatured` int(11) DEFAULT NULL,
`itincludexml` int(1) DEFAULT '1',
`boAddtojobalert` int(11) DEFAULT '1',
`vcRanking` int(11) DEFAULT '999999',
`itIncludeUkptj` int(11) DEFAULT '1',
PRIMARY KEY (`idJob`),
KEY `JOB_I_1` (`idEmployer`,`itJobstatus`),
KEY `JOB_I_2` (`itJobstatus`,`boGaptemp`,`dtJobstart`),
KEY `JOB_I_3` (`itJobstatus`,`boGraduate`,`dtJobstart`),
KEY `JOB_I_4` (`itJobstatus`,`boInternship`,`dtJobstart`),
KEY `JOB_I_5` (`itJobstatus`,`boParttimeholiday`,`dtJobstart`),
KEY `FI_JOB_idOrder_ORDER_idOrder` (`idBasket`),
KEY `FI_JOB_vcPromotioncode_PROMOTION_vcPromotioncode` (`vcPromotioncode`),
KEY `FI_JOB_idInternshiptype_ITTM_idInternshiptypemaster` (`idInternshiptypemaster`),
KEY `JOB_I_6` (`created_at`),
KEY `boEntrylevel` (`boEntrylevel`),
KEY `itJobband` (`itJobband`),
FULLTEXT KEY `index_ft_search` (`vcJobTitle`,`lvJobCompanyDescription`,`vcCompanyname`,`vcSalarydesc`,`vcCity`,`vcQualificationrequired`,`vcJobreference`,`lvJobkeywords`),
CONSTRAINT `fk_JOB_idEmployer_EMPLOYER_idEmployer` FOREIGN KEY (`idEmployer`) REFERENCES `EMPLOYER` (`idEmployer`),
CONSTRAINT `fk_JOB_idInternshiptype_ITTM_idInternshiptypemaster` FOREIGN KEY (`idInternshiptypemaster`) REFERENCES `INTERNSHIPTYPEMASTER` (`idInternshiptypemaster`),
CONSTRAINT `fk_JOB_idOrder_ORDER_idOrder` FOREIGN KEY (`idBasket`) REFERENCES `BASKET` (`idBasket`),
CONSTRAINT `fk_JOB_vcPromotioncode_PROMOTION_vcPromotioncode` FOREIGN KEY (`vcPromotioncode`) REFERENCES `PROMOTION` (`vcPromotioncode`)
) ENGINE=InnoDB AUTO_INCREMENT=1739324 DEFAULT CHARSET=latin1 |

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql Match…against query performance in InnoDB Mysql 5.6
2048
July 10, 2015 01:51AM


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.