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 ! Oh, I'm sorry. I read your CREATE statement too quickly the first time and didn't notice that the `time_result` index was across both `time` and `result`. In that case, indexing `result` separately may not help at all. Might be worth a shot, though, if you have the disk space and time to play around with it. -- 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]
