What percentage of your database contains rows where status = 200? It looks to me like MySQL is estimating that perhaps 1/3 or more of the rows have that status and therefore, it is faster to simply do a table scan rather than using an index.
On Monday 25 March 2002 1:08 pm, Michael C. Neel wrote: > 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 --------------------------------------------------------------------- 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
