(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 ;)