mysql index on varchar and date field for improve query speed of select and insert query

Question

CREATE TABLE IF NOT EXISTS `gsm_smslog220717` 
(
 `pdsid` int(11) NOT NULL auto_increment,
 `affiliateid` varchar(20) character set latin1 default NULL,
 `bpushno` longtext character set latin1,
 `psms` varchar(1) character set latin1 default NULL,
 `credits` varchar(5) character set latin1 default NULL,
 `mobileno` longtext character set latin1,
 `dlrid` longtext character set latin1,
 `dr` varchar(50) character set latin1 default NULL,
 `op` varchar(10) character set latin1 default NULL,
 `dtoken` varchar(200) character set latin1 default NULL,
 `sid` varchar(15) character set latin1 default NULL,
 `char` varchar(5) character set latin1 default NULL,
 `gid` varchar(20) character set latin1 default NULL,
 `msg` varchar(700) character set latin1 default NULL,
 `status` varchar(1) character set latin1 default NULL,
 `time` varchar(10) character set latin1 default NULL,
 `subdt` date default NULL,
 PRIMARY KEY (`pdsid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2444466;

SELECT Query:

select *
 from gsm_smslog220717
 where affiliateid ='hn137754388'
 && subdt = '2017-08-21'
 && status= 's' 

How can I improve speed of query? In one second many insert queries are also run on live.

Please help to create best index for this query.

Answer 1

MyISAM and Many insert queries ?

I definitely recommend to use InnoDB instead of MyISAM. (Don't forget to create backup before change storage)

ALTER TABLE gsm_smslog220717 ENGINE = InnoDB;

If most of your queries for gsm_smslog220717 table like that, you can create composite index for speed up Select query.

ALTER TABLE `gsm_smslog220717` ADD INDEX `affiliateid` (`affiliateid`, `subdt`, `status`);

• Rick James: Put the date last, in case another query uses a date range.