performance - Mysql InnoDb is very slow on SELECT query -

i have mysql table following structure:

mysql> show create table logs \g;  create table: create table `logs` (   `id` int(11) not null auto_increment,   `request` text,   `response` longtext,   `msisdn` varchar(255) default null,   `username` varchar(255) default null,   `shortcode` varchar(255) default null,   `response_code` varchar(255) default null,   `response_description` text,   `transaction_name` varchar(250) default null,   `system_owner` varchar(250) default null,   `request_date_time` datetime default null,   `response_date_time` datetime default null,   `comments` text,   `user_type` varchar(255) default null,   `channel` varchar(20) default 'web',    /**    other columns here....    other 18 columns here, type varchar , text    **/    primary key (`id`),   key `transaction_name` (`transaction_name`) using btree,   key `msisdn` (`msisdn`) using btree,   key `username` (`username`) using btree,   key `request_date_time` (`request_date_time`) using btree,   key `system_owner` (`system_owner`) using btree,   key `shortcode` (`shortcode`) using btree,   key `response_code` (`response_code`) using btree,   key `channel` (`channel`) using btree,   key `request_date_time_2` (`request_date_time`),   key `response_date_time` (`response_date_time`) ) engine=innodb auto_increment=59582405 default charset=utf8 

and has more 30000000 records in it.

mysql> select count(*) logs; +----------+ | count(*) | +----------+ | 38962312 | +----------+ 1 row in set (1 min 17.77 sec) 

now problem is slow, result of select takes ages fetch records table.

my following sub query takes 30 minutes fetch records of 1 day:

    select      count(sub.id) count,     date(sub.request_date_time) transaction_date,     sub.system_owner,     sub.transaction_name,     sub.response,     min(sub.response_time),     max(sub.response_time),     avg(sub.response_time),     sub.channel     (select          id,             request_date_time,             response_date_time,             timestampdiff(second, request_date_time, response_date_time) response_time,             system_owner,             transaction_name,             (case                 when response_code in ('0' , '00', 'eil000') 'success'                 else 'failure'             end) response,             channel             logs             response_code != ''             , date(request_date_time) between '2016-10-26 00:00:00' , '2016-10-27 00:00:00'             , system_owner != '') sub group date(sub.request_date_time) , sub.channel , sub.system_owner , sub.transaction_name , sub.response order date(sub.request_date_time) desc , sub.system_owner , sub.transaction_name , sub.response desc; 

i have added indexes table, still slow.

any on how can make fast ?

edit: ran above query using explain

+----+-------------+------------+------+----------------------------+------+---------+------+----------+---------------------------------+ | id | select_type | table      | type | possible_keys              | key  | key_len | ref  | rows     |                           | +----+-------------+------------+------+----------------------------+------+---------+------+----------+---------------------------------+ |  1 | primary     | <derived2> |  | null                       | null | null    | null | 16053297 | using temporary; using filesort | |  2 | derived     | logs       |  | system_owner,response_code | null | null    | null | 32106592 | using                     | +----+-------------+------------+------+----------------------------+------+---------+------+----------+---------------------------------+ 

as stands, query must scan entire table.

but first, let's air possible bug:

and date(request_date_time) between '2016-10-26 00:00:00'                                 , '2016-10-27 00:00:00' 

gives logs two days -- of 26th , all of 27th. or wanted? (between inclusive.)

but performance problem index not used because request_date_time hiding inside function (date).

jump forward better way phrase it:

and request_date_time >= '2016-10-26' , request_date_time  < '2016-10-26' + interval 1 day 

• a datetime can compared against date.
• midnight of morning of 26th included, midnight of 27th not.
• you can change 1 many days wish -- without having deal leap days, etc.
• this formulation allows use of index on request_date_time, thereby cutting severely on amount of data scanned.

as other tempting areas:

• != not optimize well, no 'composite' index beneficial.
• since can't past where, no index useful group by or order by.
• my comments date() in where not apply group by; no change needed.

why have subquery? think can done in single layer. eliminate rather large temp table. (yeah, means 3 uses of timestampdiff(), lot cheaper temp table.)

how ram? value of innodb_buffer_pool_size?

if comments not enough, , if run query (over day or on date range), can talk building , maintaining summary table, might give 10x speedup.