MySQL not using an index
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
Re: MySQL not using an index
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
RE: MySQL not using an index
This seems to be something, running it with a where domain=somedomain.com will use the domain index. Well, I'll bring out the gimp... Full query: SELECT *, access_log.domain as host_domain, access_log.server as server, DATE_FORMAT(MIN(date),'%M %e, %Y %r') as disp_date, access_log.username as user, IF (LENGTH(referer) 45, CONCAT(LEFT(referer, 42),...),referer) as short_referer, TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(MAX(date)) - TIME_TO_SEC(MIN(date)) + 30), %i:%s) as time, COUNT(access_log.url) as cnt, pIDlog_alerts.url as alert FROM access_log LEFT JOIN pIDlog_alerts ON access_log.url LIKE pIDlog_alerts.url AND access_log.server = pIDlog_alerts.server AND access_log.domain LIKE pIDlog_alerts.domain, pID_report_opt WHERE host != ip AND pID_report_opt.server = access_log.server AND LOCATE(Mozilla,browser) 0 AND LOCATE([*G_logtable*].domain,omit_domains) = 0 AND access_log.server = 'www.server.com' AND (access_log.status = '304' || access_log.status = '200') AND (filetype = html || filetype=pl || filetype=mpls || filetype=pdf || filetype=shtml) GROUP BY session_id HAVING cnt 1 ORDER BY date DESC LIMIT 20 Explain output: *** 1. row *** table: pID_report_opt type: ref possible_keys: server key: server key_len: 51 ref: const rows: 1 Extra: where used; Using temporary; Using filesort *** 2. row *** table: access_log type: ALL possible_keys: filetype,status,server key: NULL key_len: NULL ref: NULL rows: 592172 Extra: where used *** 3. row *** table: pIDlog_alerts type: ref possible_keys: server key: server key_len: 53 ref: access_log.server rows: 17 Extra: 3 rows in set (0.00 sec) Now to me, the access_log table would be best with the file_type index. Of the types selected, there are 155,983 records of the 592,341 total - or 26%. The order by with a goup by gets me a temp table, but if I could kill the filesort that would be a lot better. Also, maybe a multi field indexd would be great here, but I've neer had much luck with those, and a few times I've had results returned that were wrong with a multi column index on the table. Thanks for looking at this, Mike -Original Message- From: Christopher Thompson [mailto:[EMAIL PROTECTED]] Sent: Monday, March 25, 2002 3:13 PM To: Michael C. Neel; [EMAIL PROTECTED] Subject: Re: MySQL not using an index 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
