Mysql Match…against query performance in InnoDB Mysql 5.6 -

i need search records in table contains millions of records. have updated mysql version 5.1 5.6.

i using like in query taking around 15 sec 30 sec.

currently have modified query use match... against feature of mysql 5.6 innodb. query goes this.

select  job.idjob, job.idemployer .....      job      ( ( job.itjobstarttype=2  ,  job.dtplannedend >= '2015-07-06' )          or  ( job.itjobstarttype=1                 ,  ( ( job.itjobendtype=2 ) or  ( job.dtjobend>='2015-07-06') )               )          or  ( job.itjobstarttype=3                 ,  ( (job.dtjobend >='2015-07-06')  or  (job.itjobendtype=2) )               )             )       ,  match(vcjobtitle, lvjobcompanydescription, vccompanyname,                 vcsalarydesc, vccity, vcqualificationrequired,vcjobreference,                 lvjobkeywords )            against ('test' in natural language mode)     order  job.itjobband asc,               job.vcranking desc,               job.idjob desc,               job.fjobweight asc     limit  50;  

which takes 80- 120 seconds (3x slower )

if remove order by load in 6 10 sec.

is setting or performance tunning can apply here?

edit: using 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 set archieved',   `dtplannedend` date not null comment 'when posting job calculated. batch @ date. date on regular jobs archievd, or date on internship jobs changed listing',   `dtrequestedend` date default null comment 'exact date on employer wants jobs archieved',   `dtapplicationdeadline` date default null comment 'job converted listing on 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 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 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 online , 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 job weight ranging between 0 1 (1 being 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 | 

edit:

without sort:

with sort: