On Wed, Oct 6, 2010 at 11:15 PM, Hank <hes...@gmail.com> wrote:
> Ok, I can see that.
>
Oh, I wasn't implying that mine was necessarily better - I didn't even think
of doing it that way. I suggested a benchmark because different fuctions
might have different execution speeds. Now actually thinking about this,
I've come up with three different formulations of mine, even :-)
Given that I'm curious about this, I've bothered to do the benchmarks myself
:-) All running on the same virtual machine, a VirtualBox on my laptop,
which has 1 cpu and 384 megs of memory, running Debian 5.0.5 and MySQL
5.1.47 and using mysqlslap to perform 200.000 calls in the same thread,
iterated five times. My chosen datestamp is '2010-10-07 08:38:07'.
- select sql_no_cache unix_timestamp('2010-10-07 08:38:07') -
(unix_timestamp('2010-10-07 08:38:07') % 600);
- Average number of seconds to run all queries: 30.592 seconds
Minimum number of seconds to run all queries: 30.084 seconds
Maximum number of seconds to run all queries: 31.374 seconds
- select sql_no_cache (unix_timestamp('2010-10-07 08:38:07') div 600) *
600;
- Average number of seconds to run all queries: 29.606 seconds
Minimum number of seconds to run all queries: 29.454 seconds
Maximum number of seconds to run all queries: 29.813 seconds
- select sql_no_cache floor(unix_timestamp('2010-10-07 08:38:07') / 600)
* 600;
- Average number of seconds to run all queries: 30.857 seconds
Minimum number of seconds to run all queries: 30.274 seconds
Maximum number of seconds to run all queries: 31.378 seconds
- select sql_no_cache concat(date_format('2010-10-07 08:38:07', '%Y-%m-%d
%H:'), truncate(minute('2010-10-07 08:38:07') / 10, 0), '0');
- Average number of seconds to run all queries: 32.905 seconds
Minimum number of seconds to run all queries: 32.099 seconds
Maximum number of seconds to run all queries: 33.429 seconds
- select sql_no_cache concat(left(DATE_FORMAT('2010-10-07 08:38:07',
'%Y-%m-%d %h:%i'),15),'0');
- Average number of seconds to run all queries: 31.042 seconds
Minimum number of seconds to run all queries: 30.369 seconds
Maximum number of seconds to run all queries: 31.727 seconds
- select sql_no_cache DATE_FORMAT('2010-10-07 08:38:07', '%Y-%m-%d %h:'
), 10*(minute('2010-10-07 08:38:07')%6);
- Average number of seconds to run all queries: 32.012 seconds
Minimum number of seconds to run all queries: 31.335 seconds
Maximum number of seconds to run all queries: 32.894 seconds
So, it turns out that the method used doesn't make a major difference.
My guess would be that function execution and arithmetic is actually pretty
much instant, and that the major cost here was simply initializing the
parser and other structures. There is in any case no obvious relation
between number of function calls and execution time here.
> Here's a different approach that gets it down to
> two function calls and some math.. and the DATE_FORMAT call might not
> even be needed depending on the actual application.
>
> select
> DATE_FORMAT(start_time, "%Y-%m-%d %h:" ) as dhour,
> 10*(minute(start_time)%6) as dtime ,count(*)
> from table
> group by dhour,dtime;
>
> -Hank
>
>
>
> On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman <vegiv...@tuxera.be>
> wrote:
> > Two people already who suggested a text-based approach vs. my numeric
> > approach.
> >
> > Analysing, my method takes a single function call per record
> (to_unixtime);
> > Travis' takes 4 (concat, date_format, truncate, minute) and Hank's 3
> > (concate, left, date_format).
> >
> > Someone feel like benchmarking ? :-D
> >
> >
> >
> > On Wed, Oct 6, 2010 at 5:44 PM, Hank <hes...@gmail.com> wrote:
> >>
> >> Here's what I came up with:
> >>
> >> select concat(left(DATE_FORMAT(start_time, "%Y-%m-%d %h:%i"
> >> ),15),"0") as time, count(*) from table group by time
> >>
> >> -Hank
> >>
> >> >>
> >> >> How would one go about to construct a query that counts items within
> an
> >> >> increment or span of time, let's say increments of 10 minutes?
> >> >> Imagine a simple table where each row has a timestamp, and the query
> >> >> should return the count of items occurring within the timespan of a
> >> >> defined
> >> >> period.
> >> >>
> >> >> Say,
> >> >>
> >> >> 09:00: 14
> >> >> 09:10: 31
> >> >> 09:20: 25
> >> >> 09:30: 0
> >> >> 09:40: 12
> >> >>
> >> >> etc.
> >> >>
> >> >> I have been able to get collections of item occurrence based on month
> >> >> and
> >> >> day by using GROUP BY together with a DATE_FORMAT( start_time, "%d %m
> >> >> %Y" )
> >> >> eg.
> >> >> I can however not seem to be able to find the solution to grouping
> >> >> based
> >> >> on the minute increments in my example above.
> >> >>
> >> >> Any suggestions?
> >> >>
> >> >> --
> >> >> Kind regards
> >> >>
> >> >>
> >> >> Pascual Strømsnæs
> >> >>
> >> >> --
> >> >> MySQL General Mailing List
> >> >> For list archives: http://lists.mysql.com/mysql
> >> >> To unsubscribe:
> >> >> http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com
> >> >>
> >> >>
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=hes...@gmail.com
> >> >
> >> >
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> >>
> >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
