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*). 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
