From: Fernando Henrique Giorgetti [mailto:[EMAIL PROTECTED]
> Hi Folks! > > Here, I have the following table: > > CREATE TABLE `accesses` ( > `time` varchar(15) NOT NULL default '', > `duration` int(10) default NULL, > `user` varchar(25) NOT NULL default '', > `ipaddr` varchar(15) NOT NULL default '', > `result` varchar(30) default NULL, > `bytes` int(10) default NULL, > `reqmethod` varchar(10) default NULL, > `urlparent` varchar(100) NOT NULL default '', > KEY `usuario` (`usuario`), > KEY `time_result` (`time`, `result`) > ); > > If my table has a great number of rows (something like 5 > millions), the result time is too much longer. > > select user, count(distinct > concat(date_format(from_unixtime(time), "%d/%m/%Y"), " - ", > time_format(from_unixtime(time), "%H:%i")), ipaddr, > urlparent) as qtd, sec_to_time(sum(duration)/1000) as > duration, sum(bytes) as bytes from acessos where time >= > 1109646000 and time <= 1112324399 and result <> > "TCP_DENIED/403" group by user order by user; > > PS: explaining this select, the time_result key is a > possible_key, but, in the key field I have the NULL value > (the NULL persists even if I force with use index()). > > Can anybody help me what can I do to make this query faster > (indexes, tuning, or, change the table structure or the query). > > Thank you ! The first thing I'd do is index the `result` field, as you're checking against it in the WHERE clause. However, if I remember indexing behavior correctly, that won't help if you only have a a few unique values in that column. Give it a shot, though, I imagine it'd definitely help. If that doesn't drastically improve it, I'd also look into a way around performing the date and time functions in the query. I don't know if that's possible, but depending on what this is feeding to (most likely PHP or Perl), it may be quicker to do those calculations in the wrapping script (if there is one, that is). HTH! -- Mike Johnson Smarter Living, Inc. Web Developer www.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
