Thanks for the response. Given below is more information -

--Query 1 with im.Type = 'BISTR' 

  select count(*)
  FROM
  applicant a,
  interview_setup ist,
  companyinterview ci,
  interviewmodel im
  WHERE
   a.modifiedTime > '2007-06-00' AND
   a.id = ist.intervieweeId AND
   ist.companyInterviewId = ci.id AND
   ci.InterviewModelId = im.Id AND
im.Type = 'BISTR' 
   ;
+----------+
| count(*) |
+----------+
|     1138 |
+----------+
1 row in set (1.40 sec)

Explain of above Query
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------+----------------------------------------+---------+------------------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys                                                                                  | key                                    | key_len | ref                          | rows | Extra                    |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------+----------------------------------------+---------+------------------------------+------+--------------------------+
|  1 | SIMPLE      | im    | ref    | PRIMARY,idx_interviewmodel_type                                                                | idx_interviewmodel_type                |      15 | const                        |   36 | Using where; Using index |
|  1 | SIMPLE      | ci    | ref    | PRIMARY,idx_companyinterview_interviewmodelid                                                  | idx_companyinterview_interviewmodelid  |       4 | talentplus.im.Id             |   18 | Using index              |
|  1 | SIMPLE      | ist   | ref    | unq_interview_setup_1,idx_interview_setup_companyinterviewid,idx_interview_setup_intervieweeid | idx_interview_setup_companyinterviewid |       4 | talentplus.ci.Id             |  465 |                          |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY                                                                                        | PRIMARY                                |       4 | talentplus.ist.IntervieweeId |    1 | Using where              |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------+----------------------------------------+---------+------------------------------+------+--------------------------+


--Query 2 with im.Type NOT IN( 'OVW','PRVW')

  select count(*)
  FROM
  applicant a,
  interview_setup ist,
  companyinterview ci,
  interviewmodel im
  WHERE
   a.modifiedTime > '2007-06-00' AND
   a.id = ist.intervieweeId AND
   ist.companyInterviewId = ci.id AND
   ci.InterviewModelId = im.Id AND
im.Type NOT IN( 'OVW','PRVW') 
   ;
+----------+
| count(*) |
+----------+
|     1138 |
+----------+
1 row in set (0.20 sec)

Explain of above Query
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------+-----------------------------------+---------+-----------------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys                                                                                  | key                               | key_len | ref                               | rows   | Extra       |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------+-----------------------------------+---------+-----------------------------------+--------+-------------+
|  1 | SIMPLE      | a     | ALL    | PRIMARY                                                                                        | NULL                              |    NULL | NULL                              | 300609 | Using where |
|  1 | SIMPLE      | ist   | ref    | unq_interview_setup_1,idx_interview_setup_companyinterviewid,idx_interview_setup_intervieweeid | idx_interview_setup_intervieweeid |       4 | talentplus.a.Id                   |      1 |             |
|  1 | SIMPLE      | ci    | eq_ref | PRIMARY,idx_companyinterview_interviewmodelid                                                  | PRIMARY                           |       4 | talentplus.ist.CompanyInterviewId |      1 |             |
|  1 | SIMPLE      | im    | eq_ref | PRIMARY                                                                                        | PRIMARY                           |       4 | talentplus.ci.InterviewModelId    |      1 | Using where |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------+-----------------------------------+---------+-----------------------------------+--------+-------------+


show create table applicant ;

| applicant |CREATE TABLE `applicant` (
  `Id` int(11) NOT NULL auto_increment,
  `PersonDetailsId` int(11) NOT NULL default '-1',
  `CompanyId` int(11) NOT NULL default '-1',
  `CompanyJobCategoryId` int(11) default NULL,
  `CompanyLocationId` int(11) default NULL,
  `Source` varchar(50) collate utf8_unicode_ci default NULL,
  `SourceType` varchar(5) collate utf8_unicode_ci default NULL,
  `CreatedTime` datetime default NULL,
  `ModifiedTime` datetime default NULL,
  `ApplyStatus` varchar(5) collate utf8_unicode_ci default NULL,
  `WillReportTo` int(11) default NULL,
  `SendResultToATS` char(1) collate utf8_unicode_ci default 'N',
  `SyncDummy` int(1) default '0',
  PRIMARY KEY  (`Id`),
  KEY `applicant_fk_7` (`WillReportTo`),
  KEY `idx_applicant_persondetailsid` (`PersonDetailsId`),
  KEY `idx_applicant_companyid` (`CompanyId`),
  KEY `idx_applicant_companyjobcategoryid` (`CompanyJobCategoryId`),
  KEY `idx_applicant_companylocationid` (`CompanyLocationId`),
  KEY `idx_applicant_sourcetype` (`SourceType`),
  KEY `idx_applicant_createdtime` (`CreatedTime`),
  KEY `idx_applicant_applystatus` (`ApplyStatus`),
  KEY `idx_applicant_SyncDummy` (`SyncDummy`),
  CONSTRAINT `applicant_fk_1` FOREIGN KEY (`PersonDetailsId`) REFERENCES `persondetails` (`Id`),
  CONSTRAINT `applicant_fk_2` FOREIGN KEY (`CompanyId`) REFERENCES `company` (`Id`),
  CONSTRAINT `applicant_fk_3` FOREIGN KEY (`CompanyJobCategoryId`) REFERENCES `companyjobcategory` (`Id`),
  CONSTRAINT `applicant_fk_4` FOREIGN KEY (`CompanyLocationId`) REFERENCES `companylocations` (`Id`),
  CONSTRAINT `applicant_fk_7` FOREIGN KEY (`WillReportTo`) REFERENCES `organization_hierarchy` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

show create table interview_setup ;

| interview_setup |CREATE TABLE `interview_setup` (
  `Id` int(11) NOT NULL auto_increment,
  `IntervieweeId` int(11) NOT NULL default '-1',
  `CreatedBy` int(11) NOT NULL default '0',
  `IntervieweeType` varchar(5) collate utf8_unicode_ci NOT NULL default '-1',
  `CompanyInterviewId` int(11) NOT NULL default '-1',
  `JobDescriptionRequested` char(1) collate utf8_unicode_ci default NULL,
  `ResumeRequested` char(1) collate utf8_unicode_ci default NULL,
  `SetupTime` datetime default NULL,
  `Status` varchar(5) collate utf8_unicode_ci default NULL,
  `InterviewPurpose` varchar(5) collate utf8_unicode_ci default NULL,
  `InterviewLocaleId` int(11) default NULL,
  `notifications` int(11) NOT NULL default '4',
  `allowClientAccess` char(1) collate utf8_unicode_ci default NULL,
  `onCompleteNotifications` int(11) default NULL,
  `InterviewResultsToComments` text collate utf8_unicode_ci,
  `SyncDummy` int(1) default '0',
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `unq_interview_setup_1` (`IntervieweeId`),
  KEY `interview_setup_fk_5` (`CreatedBy`),
  KEY `interview_setup_fk_6` (`InterviewLocaleId`),
  KEY `idx_interview_setup_companyinterviewid` (`CompanyInterviewId`),
  KEY `idx_interview_setup_intervieweeid` (`IntervieweeId`),
  KEY `idx_interview_setup_intervieweeType` (`IntervieweeType`),
  KEY `idx_interview_setup_SyncDummy` (`SyncDummy`),
  CONSTRAINT `interview_setup_fk_1` FOREIGN KEY (`CompanyInterviewId`) REFERENCES `companyinterview` (`Id`),
  CONSTRAINT `interview_setup_fk_4` FOREIGN KEY (`IntervieweeId`) REFERENCES `applicant` (`Id`),
  CONSTRAINT `interview_setup_fk_5` FOREIGN KEY (`CreatedBy`) REFERENCES `user` (`Id`),
  CONSTRAINT `interview_setup_fk_6` FOREIGN KEY (`InterviewLocaleId`) REFERENCES `locale` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |


show create table companyinterview ;

| companyinterview |CREATE TABLE `companyinterview` (
  `Id` int(11) NOT NULL auto_increment,
  `Name` varchar(100) collate utf8_unicode_ci NOT NULL default '-1',
  `CompanyId` int(11) NOT NULL default '-1',
  `InterviewModelId` int(11) NOT NULL default '-1',
  `InterviewerVerificationMode` varchar(5) collate utf8_unicode_ci default NULL,
  `InterviewEditDuration` int(11) default NULL,
  `ResultDisplayMode` varchar(5) collate utf8_unicode_ci default NULL,
  `ThemeGroupId` int(11) default NULL,
  `InterviewRecordingMode` varchar(5) collate utf8_unicode_ci default NULL,
  `forceBenchmarkSelection` char(1) collate utf8_unicode_ci default NULL,
  `EstimatedTime` int(11) default NULL,
  `Status` varchar(5) collate utf8_unicode_ci NOT NULL default 'ACTV',
  `LockoutPeriod` int(11) default NULL,
  `IsFileUploadRequired` char(1) collate utf8_unicode_ci default 'N',
  `IsReviewInterview` char(1) collate utf8_unicode_ci default 'N',
  `SequenceNumber` int(11) default '0',
  `IsDisableInterview` char(1) collate utf8_unicode_ci default 'N',
  PRIMARY KEY  (`Id`),
  KEY `companyinterview_fk_2` (`CompanyId`),
  KEY `companyinterview_fk_3` (`ThemeGroupId`),
  KEY `idx_companyinterview_interviewmodelid` (`InterviewModelId`),
  CONSTRAINT `companyinterview_fk_1` FOREIGN KEY (`InterviewModelId`) REFERENCES `interviewmodel` (`Id`),
  CONSTRAINT `companyinterview_fk_2` FOREIGN KEY (`CompanyId`) REFERENCES `company` (`Id`),
  CONSTRAINT `companyinterview_fk_3` FOREIGN KEY (`ThemeGroupId`) REFERENCES `themegroup` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |


show create table interviewmodel ;

| interviewmodel | CREATE TABLE `interviewmodel` (
  `Id` int(11) NOT NULL auto_increment,
  `Type` varchar(5) collate utf8_unicode_ci NOT NULL default '-1',
  `Keywords` varchar(200) collate utf8_unicode_ci default NULL,
  `Copyright` varchar(200) collate utf8_unicode_ci default NULL,
  `EstimatedTime` int(11) default NULL,
  `SubType` varchar(5) collate utf8_unicode_ci NOT NULL default 'DEFLT',
  `IsInterviewRepeatableForPerson` char(1) collate utf8_unicode_ci default 'N',
  `TotalScore` int(11) default NULL,
  PRIMARY KEY  (`Id`),
  KEY `idx_interviewmodel_type` (`Type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |