You could probably save a bit of processing time by changing: concat(date_format(from_unixtime(time), "%d/%m/%Y")," - ", time_format(from_unixtime(time), "%H:%i"))
to: date_format(from_unixtime(time), "%d/%m/%Y - %H:%i") This would mean half the date conversions would be executed. Separating out the 'time' and 'result' indicies will probably help too. Cheers, Andrew On 14/4/05 6:34 pm, "Fernando Henrique Giorgetti" <[EMAIL PROTECTED]> wrote: > 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 ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
