Public feed
#721861
(selecting against the current archive table)
mysql> select filename,count(filename) AS hits from app_log WHERE request_type='read' AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_time GROUP BY filename ORDER BY hits DESC;
<snip>
+-------------------------------------------------------------------------------+------+
3269 rows in set (3.39 sec)
(selecting against new myisam table, same data)
mysql> select filename,count(filename) AS hits from app_log_fast WHERE request_type='read' AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_time GROUP BY filename ORDER BY hits DESC;
+-------------------------------------------------------------------------------+------+
3269 rows in set (0.10 sec)
Note the amount of time - 3.39 seconds vs. 0.10 seconds, and I even performed the first query twice, so it has the head-start of query caching to give it an advantage.
Using explain:
mysql> explain select filename,count(filename) AS hits from app_log WHERE request_type='read' AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_time GROUP BY filename ORDER BY hits DESC;
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | app_log | ALL | NULL | NULL | NULL | NULL | 1036462 | Using where; Using temporary; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------------------------------------+
1 row in set (0.00 sec)
What explain says is there are no indexes, the rows it has to sort and group by are 1M+,
mysql> explain select filename,count(filename) AS hits from app_log_fast WHERE request_type='read' AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_time GROUP BY filename ORDER BY hits DESC;
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+----------------------------------------------+
| 1 | SIMPLE | app_log_fast | ref | request_type | request_type | 22 | const | 19661 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+--------------+---------+-------+-------+----------------------------------------------+
1 row in set (0.01 sec)
This explain says we are using request_type, and only have to sort and group 19,661 rows.
When adding queries, explain is a valueble tool to see if what your adding is efficient. Please use it ;)