I'm nearing the end of my rope trying to figure this one out. I have
some queries run against a table that is a log of hits from a web
server. No matter how simple I've tried to make my query, MySQL sill
does not choose an index. Even use index has no effect.
My table:
CREATE TABLE access_log (
id bigint(20) unsigned NOT NULL auto_increment,
date datetime default NULL,
host varchar(255) default NULL,
domain varchar(255) default NULL,
ip varchar(255) default NULL,
method varchar(255) default NULL,
port smallint(6) default NULL,
url varchar(255) default NULL,
urlpath varchar(255) default NULL,
filepath varchar(255) default NULL,
filename varchar(255) default NULL,
filetype varchar(255) default NULL,
referer varchar(255) default NULL,
referer_domain varchar(255) default NULL,
referer_query varchar(255) default NULL,
browser varchar(255) default NULL,
status int(11) default NULL,
bytes int(11) default NULL,
server varchar(255) default NULL,
session_id varchar(255) default NULL,
user_id varchar(255) default NULL,
username varchar(255) default NULL,
query varchar(255) default NULL,
path_info varchar(255) default NULL,
request varchar(255) default NULL,
PRIMARY KEY (id),
KEY domain (domain(10)),
KEY url (url(10)),
KEY filetype (filetype(5)),
KEY status (status),
KEY session_id (session_id(10)),
KEY user_id (user_id(10)),
KEY ip (ip(5)),
KEY host (host(5)),
KEY server (server(10))
) TYPE=MyISAM;
The query:
SELECT * FROM access_log WHERE status=200
Gives this result in explain:
*************************** 1. row ***************************
table: MEP_access_log
type: ALL
possible_keys: status
key: NULL
key_len: NULL
ref: NULL
rows: 592042
Extra: where used
1 row in set (0.00 sec)
I'm really running a much more complex query, but this simple one
doesn't use an index so I don't think my query is affecting the index
(again, I don't *think*). I've run analyze table a few times, and
installed mysql 4.0.1-alpha (currently running 3.23.36), but with no
change. Any idea or routes to look down would greatly help.
Thanks,
Mike
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
