mysql - Select columns from table1 join table2, taking very long time -

i have 2 tables. these 2 tables trying insert records third table using select query join. found select query join not using indexes , taking lots of time, hence insertion slow.
tried create multiple indexes suggested in few posts not avail.
mysql join not using index
mysql query join not using index

here tables structure:

create table master_table ( id bigint(20) unsigned not null auto_increment, field1 varchar(50) default null, field2 varchar(50) default null, field3 varchar(50) default null, field4 varchar(50) default null, primary key (id), key mt_field1_index (field1) ) engine=innodb default charset=utf8;  create table child_table ( c_id bigint(20) unsigned not null auto_increment, m_id bigint(20) unsigned not null , group_id bigint(20) unsigned not null , status enum('status1','status2','status3') not null, job_id varchar(50) default null, primary key (c_id), unique key ct_mid_gid (m_id,group_id), key index_ct_status (status), key index_ct_jobid (job_id), key index_ct_mid (m_id), key index_ct_cid_sts_tsk (group_id,status,job_id) ) engine=innodb default charset=utf8;   

query:

---

select m.id      , nullif(trim(m.field1),'')   master_table m    join child_table c      on m.id = c.m_id   c.group_id = 2     , c.status = 'status3'     , c.job_id = 0   order      m.id    limit 0, 1000; 

explain:

---

+-------+-------------+-------+------------+----------+------------------------------------------------------------------------------+-----------------+---------+--------------+-------+----------+------------------------------------------------+ |    id | select_type | table | partitions |   type   |                                possible_keys                                 |       key       | key_len |     ref      | rows  | filtered |                                          | +-------+-------------+-------+------------+----------+------------------------------------------------------------------------------+-----------------+---------+--------------+-------+----------+------------------------------------------------+ |     1 | simple      |    c  |    (null)  |   ref    |  ct_mid_gid,index_ct_status,index_ct_jobid,index_ct_mid,index_ct_cid_sts_tsk | index_ct_status |       1 | const        | 65689 |     0.00 | using where; using temporary; using filesort   | |     1 | simple      |    m  |    (null)  |   eq_ref | primary                                                                      | primary         |       8 | r_n_d.c.m_id |     1 |   100.00 | (null)                                         | +-------+-------------+-------+------------+----------+------------------------------------------------------------------------------+-----------------+---------+--------------+-------+----------+------------------------------------------------+ 

 c.group_id = 2     , c.status = 'status3'     , c.job_id = 0   order c.m_id     -- note change 

needs

 index(group_id, status, job_id,   -- in order        m_id)                       -- last 

what have (separate indexes) not same.

in order limit index must entirely past where , order by. prevents computing rows (before limit) , sorting , limit.

so, 4 speedups:

• index efficiently fetching desired rows (from c)
• no need sort pass (since order by delivers them in order)
• the index "covering" (hence, no bouncing , forth between index btree , data btree c)
• get stop @ 1000.

while @ it, consider getting rid of auto_increment. toss c_id , change

primary key (c_id), unique key ct_mid_gid (m_id, group_id) 

-->

primary key(m_id, group_id) 

coincidentally, if had done this, key index_ct_cid_sts_tsk (group_id,status,job_id) have stumbled perfect index. because pk implicitly tacked onto secondary index, need m_id, not c_id. anyway, prefer explicit.

and when making changes, toss redundant indexes. example, key index_ct_mid (m_id) useless since beginning of index.