Re: update and times
On Wed, 06 Oct 2010 17:48:55 -0400 kalin m ka...@el.net wrote: Simcha Younger wrote: executing this query didn't update the record. why? The two values you have here are equal: sample data : 12862162510269684 query: where unix_time 12862162510269684 and therefore the 'less than' query did not match that row. sorry... not following the value in the table was 12862162385941345. the time in the query was 12862162510269684. Sorry, I misread your question. did you check before you ran the query that this is the only matching record for your condition? It is possible that the limit 1 is preventing the update because you have more than one record less than the timestamp in the query. You might also want to add to your where condition: AND `updated` = 0; so it will skip rows which have already been updated. -- Simcha Younger sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Constructing query to display item count based on increments of time
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:
RE: my.ini
Open you're my.ini and correct the values as stated in the event viewer Check the online documentation for the keys and values to set. http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html Regards -Original Message- From: ext Elim PDT [mailto:e...@pdtnetworks.net] Sent: 07 October 2010 04:51 To: mysql@lists.mysql.com Subject: my.ini I recently upgraded mysql from 5.1.9-beta to 5.1.50 Since I used the custom data dir path, my upgrade is basically a replacement of the bin folder and I used the old my.ini file that pointing the data dir to the right path. Then I found, in the event viewer, two warnings: (1) --myisam_max_extra_sort_file_size is deprecated and does nothing in this version. It will be removed in a future release. (2) '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. I mainly used MyISAM tables but have a few of innodb tables. What should I do to correct the my.ini? Thanks for your help. EL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query working on 5.1.43 and not 5.0.77
Hi, My hosting company are only running MySQL version 5.0.77 and at this current time are not planning on upgrading to at least my required version of 5.1.43. My query in which I have some problems is shown below : SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,@team ,IF(@team teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM players JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND players.teams_id 0 ORDER BY players.teams_id, players_master.rating DESC) s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC Basically the part of the statement WHERE rank = 11 has no affect in version 5.0.77, but gives the desired results in version 5.1.43. Does anyone have any suggestions on how to overcome this problem, since at the current time I don't have the budget to move to another hosting company and this is the only query which I have a problem with. Thanks for any help. Cheers Neil
Fwd: Design advice
Wonder if anyone can help me ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Tue, Oct 5, 2010 at 10:07 AM Subject: Design advice To: [MySQL] mysql@lists.mysql.com Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Thanks, Neil
Re: my.ini
Thanks a lot Andrew. - Original Message - From: andrew.2.mo...@nokia.com To: e...@pdtnetworks.net; mysql@lists.mysql.com Sent: Thursday, October 07, 2010 3:07 AM Subject: RE: my.ini Open you're my.ini and correct the values as stated in the event viewer Check the online documentation for the keys and values to set. http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html Regards -Original Message- From: ext Elim PDT [mailto:e...@pdtnetworks.net] Sent: 07 October 2010 04:51 To: mysql@lists.mysql.com Subject: my.ini I recently upgraded mysql from 5.1.9-beta to 5.1.50 Since I used the custom data dir path, my upgrade is basically a replacement of the bin folder and I used the old my.ini file that pointing the data dir to the right path. Then I found, in the event viewer, two warnings: (1) --myisam_max_extra_sort_file_size is deprecated and does nothing in this version. It will be removed in a future release. (2) '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead. I mainly used MyISAM tables but have a few of innodb tables. What should I do to correct the my.ini? Thanks for your help. EL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=e...@pdtnetworks.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.862 / Virus Database: 271.1.1/3182 - Release Date: 10/07/10 00:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mySQL vs. NoSQL
You guys hear talk about NoSQL and here's a good article on the topic especially as to how it pertains to mySQL... http://www.linuxjournal.com/article/10770 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
