This seems to work for what I have in mind: subsecond returns (though with a pretty small dataset).
DROP TABLE IF EXISTS `access_log`; CREATE TABLE `access_log` ( id mediumint AUTO_INCREMENT, agent varchar(255), bytes_sent int(10), child_pid smallint(5) unsigned, cookie varchar(255), machine_id varchar(25), request_file varchar(255), referer varchar(255), remote_host varchar(50), remote_logname varchar(50), remote_user varchar(50), request_duration smallint(5) unsigned, request_line varchar(255), request_method varchar(10), request_protocol varchar(10), request_time varchar(28), request_uri varchar(255), request_args varchar(255), server_port smallint(5) unsigned, ssl_cipher varchar(25), ssl_keysize smallint(5) unsigned, ssl_maxkeysize smallint(5) unsigned, status smallint(5) unsigned, time_stamp timestamp(15), virtual_host varchar(255), FULLTEXT KEY request_uri (request_uri), PRIMARY KEY (id), INDEX ip_timestamp (remote_host,time_stamp), );
So the ultimate goal of this is to be able to pull out the top 10 or 25 most-requested URLs. I’m guessing I need to grab the URLs (more likely the entry numbers) and then query the wp database for the title, then write out my list of URL, linked by title, and the count.
Right now, I am loading the data for 2006 for some more tests. Then I’ll pick this up again.
Thanks for all your help.
<update> I’m not sure this is do-able, given the constraints of my systems here. Once the dataset gets to a meaningful size (like 300k records — this year’s volume, so far), queries are quite slow. It shouldn’t take 30+ seconds to fetch and order 10 rows from a 300k row table. I’m sure it’s tunable or something a competent database designer would fix. I’ll get back to it after awhile.
mysql> select request_uri, count(request_uri) as count from access_log group by request_uri order by count desc limit 25; +-------------------------------------------------------------------------------+-------+ | request_uri | count | +-------------------------------------------------------------------------------+-------+ | /wordpress/ | 39 | | /wordpress/index.php/archives/2005/12/07/seventh-installment/ | 29 | | /wordpress/index.php/archives/2006/03/07/indexes/ | 14 | | /wordpress | 13 | | /wordpress/index.php/archives/2006/03/07/index-from-the-creation/ | 12 | | /wordpress/index.php/archives/2005/04/11/your-wish-is-my-command/ | 12 | | /wordpress/index.php/archives/2006/03/06/what-makes-an-sf-book-an-sf-book/ | 11 | | /wordpress/index.php/archives/2005/12/04/fourth-installment/ | 9 | | /wordpress/index.php/archives/2006/02/13/ | 9 | | /wordpress/index.php?p=211 | 8 | | /wordpress/index.php?cat=10 | 7 | | /wordpress/index.php/archives/2005/04/12/welcome-boing-boing-visitors/ | 7 | | /wordpress/index.php/archives/2006/03/06/mousepigyou/ | 6 | | /wordpress/index.php?p=1260 | 5 | | /wordpress/index.php/archives/2006/03/05/omnibus-post/ | 5 | | /wordpress/index.php/archives/category/obscure-pursuits/ | 5 | | /wordpress/index.php/archives/2005/04/12/welcome-boing-boing-visitors/?cat=24 | 5 | | /wordpress/index.php?p=2430 | 4 | | /movabletype/ | 4 | | /wordpress/index.php | 4 | | /wordpress/index.php?p=1568 | 4 | | /wordpress/index.php?p=2107 | 4 | | /wordpress/index.php?p=1376 | 4 | | /wordpress/index.php/archives/2006/03/05/transportation-migrations-growth/ | 4 | | /wordpress/index.php/archives/2005/06/24/make-money-fast/ | 4 | +-------------------------------------------------------------------------------+-------+ 25 rows in set (0.00 sec) mysql>
Now playing: Starting To Come To Me by Elvis Costello from the album “All This Useless Beauty” | Get it