# Here is a table definition for storing log entries in apache combined style format
CREATE TABLE `entry` (
`entryid` CHAR(50) PRIMARY KEY,
`vhost` VARCHAR(128),
`host` VARCHAR(15),
`ident` VARCHAR(25),
`user` VARCHAR(50),
`date` DATETIME,
`timezone` VARCHAR(5),
`resource` TEXT,
`status` VARCHAR(3),
`bytes` BIGINT,
`referrer` TEXT,
`user_agent` TEXT,
`cookie` TEXT,
INDEX (vhost, host, date, timezone, resource(255), referrer(255), cookie(255))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;
#You can see that `date` is indexed. However, whenever I try to execute a select using that field,
#I get a full scan instead of an indexed lookup.
#for instance, look at this explain: EXPLAIN SELECT cookie FROM entry WHERE date >= '2007-06-10 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | entry | ALL | NULL | NULL | NULL | NULL | 30903791 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
#I've tried a couple of alternative ways of specifying the query, like this one:
#EXPLAIN SELECT cookie FROM entry WHERE date BETWEEN '2007-06-10 00:00:00' AND '2007-06-11 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | entry | ALL | NULL | NULL | NULL | NULL | 30903791 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
#As you can see, It has to individually examin ~31 million records to find what I'm asking for.
#What Am I doing wrong?