mysql - How do I properly index a query that selects multiple fields when using GROUP BY and ORDER BY? -

my issue

we have email engine sends users emails either in blocks (same email many users: ie, bulk newsletter) or singleton cases (one email 1 user: ie, password reset). have admin console displays log of emails have been sent out.

right now, table has around 750,000 rows. every email sent, following logged in table (trimmed down main items):

• id
• batch_id
• user_id
• subject
• send_date
• created

if batch email sent out, emails in batch have same batch_id. when admin browsing logs console, don't want display emails batch within table results - unique one. did this:

select id, batch_id, user_id, subject, send_date, created `emails`  group batch_id order created desc limit 10 

this works, slow. result of our explain on our query:

+----+-------------+-------------+-------+---------------+----------+---------+------+------+---------------------------------+ | id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows |                           | +----+-------------+-------------+-------+---------------+----------+---------+------+------+---------------------------------+ |  1 | simple      | emails_logs | index | null          | batch_id | 17      | null | 2522 | using temporary; using filesort | +----+-------------+-------------+-------+---------------+----------+---------+------+------+---------------------------------+ 1 row in set (0.00 sec) 

our goal

select unique records batch_id, ordered date records created in descending order, quickly.

my question is: how index these in best way, , select fields in appropriate manner take advantage of these indexes?

thank you!

here idea. create index on emails(created, batchid) , on emails(batchid). do:

select e.id, e.batch_id, e.user_id, e.subject, e.send_date, e.created (select batchid, created       emails       order created desc       limit 10      ) e10 join      emails e      on e.batchid = e10.batchid group e.batchid order created desc; 

the idea reduce set processing, before doing aggregation , ordering.

it sounds data better if more normalized. 1 table should have batch information, including sender , created date. other should have