Not the answer you need?
Register and ask your own question!

MySql Query Optimization Required

MrAwanishMrAwanish EntrantInactive User Role Beginner
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
# [email protected]: 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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.