0
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.

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`);
  • Put the date last, in case another query uses a date range. – Rick James Nov 2 '17 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.