Fernando Henrique Giorgetti <[EMAIL PROTECTED]> wrote on 04/14/2005 
02:34:30 PM:

> 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 !
> -- 
> Fernando Henrique Giorgetti
> [EMAIL PROTECTED]
> Departamento de Tecnologia
> http://www.gruponet.com.br
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]
> 

If I unfold and indent your query I get:

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;

Your COUNT() operator seems to be trying to execute a COUNT((concatenated 
date to nearest minute), ipaddr, urlparent). I may have unfolded it 
incorrectly but that's how it seems to me. I think you meant to put the 
ipaddr and urlparent fields INTO the CONCAT() but I am just working from 
what I got.

There is a faster way to compute time to the nearest minute than what you 
are doing with the string conversions. Just do an integer division of your 
TIME value by 60 and throw away the remainder like this:

time DIV 60

or like this:
FLOOR(time/60)

(http://dev.mysql.com/doc/mysql/en/arithmetic-functions.html)

If I understand your COUNT(DISTINCT ) statement correctly, you want to 
know how in how many different minutes the user used either a unique 
ipaddr or a different urlparent. Am I close? You can also compute <> as 
the OR of two ranges (which may end up using the index or it may not...) 
So this could be a valid revision of your original query:

select user
        , count(distinct 
                concat(
                        FLOOR(time/60)
                        , 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' 
                OR result > 'TCP_DENIED/403'
        )
group by user;

Note: GROUP BY includes a free ORDER BY unless you specify otherwise.

HTH,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Reply via email to