I have been playing with indexes in MySQL today, and found a good example of their benefits:
select request_uri, count(request_uri) as count from access_log group by request_uri order by count desc limit 25;
coughed up results thusly:
25 rows in set (10 min 38.19 sec)
This is against a table with 329,000 rows.
With the addition of this:
alter table access_log add index request_uri_index (request_uri);
we get these results:
25 rows in set (26.98 sec)
Not blinding fast, but a considerable improvement.
The table creation syntax is here: I don’t use all these tables, since I cribbed the schema from someone else. I don’t suppose it hurts to create them.
DROP TABLE IF EXISTS `access_log`;
CREATE TABLE `access_log` (
`id` varchar(19) default NULL,
`agent` varchar(255) default NULL,
`bytes_sent` int(10) unsigned default NULL,
`child_pid` smallint(5) unsigned default NULL,
`cookie` varchar(255) default NULL,
`machine_id` varchar(25) default NULL,
`request_file` varchar(255) default NULL,
`referer` varchar(255) default NULL,
`remote_host` varchar(50) default NULL,
`remote_logname` varchar(50) default NULL,
`remote_user` varchar(50) default NULL,
`request_duration` smallint(5) unsigned default NULL,
`request_line` varchar(255) default NULL,
`request_method` varchar(10) default NULL,
`request_protocol` varchar(10) default NULL,
`request_time` varchar(28) default NULL,
`request_uri` varchar(255) default NULL,
`request_args` varchar(255) default NULL,
`server_port` smallint(5) unsigned default NULL,
`ssl_cipher` varchar(25) default NULL,
`ssl_keysize` smallint(5) unsigned default NULL,
`ssl_maxkeysize` smallint(5) unsigned default NULL,
`status` smallint(5) unsigned default NULL,
`time_stamp` int(10) unsigned default NULL,
`virtual_host` varchar(255) default NULL,
KEY `request_uri_index` (`request_uri`),
FULLTEXT KEY `request_uri` (`request_uri`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `cookies`;
CREATE TABLE `cookies` (
`id` varchar(19) default NULL,
`item` varchar(80) default NULL,
`val` varchar(80) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `headers_in`;
CREATE TABLE `headers_in` (
`id` varchar(19) default NULL,
`item` varchar(80) default NULL,
`val` varchar(80) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `headers_out`;
CREATE TABLE `headers_out` (
`id` varchar(19) default NULL,
`item` varchar(80) default NULL,
`val` varchar(80) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `notes`;
CREATE TABLE `notes` (
`id` varchar(19) default NULL,
`item` varchar(80) default NULL,
`val` varchar(80) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;