MySql Query Optimization Required

MySQL & MariaDB Other MySQL® Questions
1

Hi,

MySql query -

SELECT SUM(clickNo), SUM(earn), DATE(datetime) FROM table1 GROUP BY DATE(datetime);

OUTPUT -

±-------------±----------±---------------+
| SUM(clickNo) | SUM(earn) | DATE(datetime) |
±-------------±----------±---------------+
| 20919 | 2091.70 | 2016-06-18 |
| 26321 | 2631.50 | 2016-06-19 |
| 18110 | 1810.00 | 2016-06-20 |
| 26171 | 2614.70 | 2016-06-21 |
| 26874 | 2686.20 | 2016-06-22 |
| 1 | 0.10 | 2016-06-23 |
±-------------±----------±---------------+
6 rows in set (3.14 sec)

SLOW QUERY LOG OUTPUT -

Time: 160707 13:24:02

User@Host: root[root] @ localhost

Thread_id: 4 Schema: paisalele QC_hit: No

# Query_time: 3.139892 Lock_time: 0.000131 Rows_sent: 6 Rows_examined: 236644

Rows_affected: 0

EXPLAIN SELECT SUM(clickNo), SUM(earn), DATE(datetime) FROM table1 GROUP BY DATE(datetime);

OUTPUT -

±-----±------------±-------±-----±--------------±-----±--------±-----±-------±--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±-----±------------±-------±-----±--------------±-----±--------±-----±-------±--------------------------------+
| 1 | SIMPLE | clicks | ALL | NULL | NULL | NULL | NULL | 231899 | Using temporary; Using filesort |
±-----±------------±-------±-----±--------------±-----±--------±-----±-------±--------------------------------+

NOW, How do I optimize the query, so that it’ll not loged in slow query log, here query result is only 6 but, Rows_examined 236644

Thanks