All records scanning using "LIKe" and "IN" operaters
The query taking too much to fetch 1196900 records, iam pasting the query, table structure and count
mysql> explain select right(msisdn,10) MSISDN,upper(circle) CIRCLE,upper(status) STATUS, upper(service_name) SRVICE_KEY,upper(transaction_id) TRANSACTION_ID, 'NA' PREPAID_FLAG,'NA' LAST_CHARGED_AMOUNT, request_date START_DATE, 'NA' LAST_CHARGED_DATE ,'NA' RENEWAL_DATE, 'NA' END_DATE,unique_id,songid from se_subscription_details where service_name like 'rn_%' and upper(circle) in ('RJ','BH') and status in ('AVR','CSR')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: se_subscription_details
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1196259
Extra: Using where
1 row in set (0.00 sec)
mysql> show create table se_subscription_details\G
*************************** 1. row ***************************
Table: se_subscription_details
Create Table: CREATE TABLE `se_subscription_details` (
`id` int(10) unsigned NOT NULL auto_increment,
`msisdn` varchar(15) NOT NULL default '',
`service_id` varchar(20) default NULL,
`service_name` varchar(50) NOT NULL default '',
`subscriber_type` text,
`subscriber_class` text,
`circle` varchar(50) default NULL,
`charged_status` varchar(20) default NULL,
`service_description` text,
`cp` varchar(50) default NULL,
`transaction_id` varchar(20) NOT NULL default '',
`auto_renewable_flag` char(1) default NULL,
`price_point` int(10) default NULL,
`charged_amt` varchar(10) default NULL,
`request_date` datetime default NULL,
`start_date` datetime default NULL,
`end_date` datetime default NULL,
`renewal_date` datetime default NULL,
`channel` varchar(50) NOT NULL default '',
`short_code` varchar(20) default NULL,
`status` varchar(20) default NULL,
`uup_start_time` datetime default NULL,
`uup_end_time` datetime default NULL,
`autotimestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`groupid` varchar(20) default NULL,
`keyword` varchar(100) default NULL,
`rec_base` char(1) default NULL,
`tnb_flag` char(1) default NULL,
`category` varchar(2) default NULL,
`search_tag` text,
`originator` varchar(30) default NULL,
`songid` varchar(255) default 'X',
`targetmdn` varchar(255) default 'X',
`unique_id` varchar(50) NOT NULL default 'X',
`rbt_type` varchar(5) NOT NULL default 'X',
`is_rbt_service` varchar(5) NOT NULL default 'X',
`optinsent` int(10) NOT NULL default '0',
`optinsent_date` datetime default NULL,
`optinreply_date` datetime default NULL,
`optin_msg_type` varchar(30) NOT NULL default 'X',
`reminder_date` datetime default NULL,
`optinsentshortcode` varchar(30) NOT NULL default 'X',
`optin_content` text,
`source` varchar(50) NOT NULL default '',
`ischildcallback` varchar(10) NOT NULL default 'X',
`defaultparent` varchar(50) NOT NULL default 'X',
`parentrefid` varchar(50) NOT NULL default 'X',
PRIMARY KEY (`id`),
UNIQUE KEY `ums` (`msisdn`,`service_name`),
KEY `umsid` (`msisdn`,`service_id`),
KEY `trai_consent` (`optinsent`,`optinsent_date`,`reminder_date`)
) ENGINE=MyISAM AUTO_INCREMENT=76218719 DEFAULT CHARSET=utf8