- Someone
- Monday, June 11th, 2007 at 1:29:10pm MDT
- # 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?
advertising
Update the Post
Either update this post and resubmit it with changes, or make a new post.
You may also comment on this post.
Please note that information posted here will expire by default in one month. If you do not want it to expire, please set the expiry time above. If it is set to expire, web search engines will not be allowed to index it prior to it expiring. Items that are not marked to expire will be indexable by search engines. Be careful with your passwords. All illegal activities will be reported and any information will be handed over to the authorities, so be good.
