mysql - Trying to optimize graph query on massive table -

i'm trying find way query huge table graph statistics. here's how i'm forming graph data. day on x-axis , amount y-axis.

select date_format(earning_created, '%c/%e/%y') day, count(earning_link_id) amount  earnings earning_link_id = 1093 group date(earning_created) 

took 21.8s....earnings has 550,000 rows.

i'm in process of setting read replica server, brute force, not elegant query approach.

i wonder how can optimize type of query, can graph data quickly?

as suggested in comments, table may lacking of index.

the obvious earning_link_id it's column used in where condition.

as side note when have performance issues on requests and/or want optimize them, use explain , explain extended. show how mysql scans table answer query. if table has no index filtered columns (where condition) scan rows costly; whereas index on earning_link_id mysql don't need scan whole table find affected rows (for query).