Re: update and times
On Mon, 04 Oct 2010 16:11:08 -0400 kalin m ka...@el.net wrote: what i'm trying to do is update the column only of one of those times isn't yet passed. and it works. except sometimes... like these 2 unix times: this was in the table under unix time: 12862162385941345... this 12862162510269684 got passed in the update command as in: update the_table set updated = 1 where unix_time 12862162510269684 limit 1; 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. -- 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: Indexing question
Hi
Find attached the first of my queries, I require to be checked over. Let me
know if you need any more information, I've included the query, EXPLAIN
output, and the relavant SHOW CREATE TABLE
Regards
Neil
On Mon, Oct 4, 2010 at 9:50 PM, Neil Tompkins
neil.tompk...@googlemail.comwrote:
I've got a fair few number of queries to be checked over. Will send them
tommorrow
On 4 Oct 2010, at 18:27, Gavin Towey gto...@ffn.com wrote:
Include the query, EXPLAIN output, and the relavant SHOW CREATE TABLE
table \G output. Someone should be able to offer suggestions.
-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Monday, October 04, 2010 8:54 AM
To: Joerg Bruehe
Cc: [MySQL]
Subject: Re: Indexing question
Jörg
Thanks for the useful reply. Maybe I can EXPLAIN my select queries for
you
to advise if any changes need to be made ?
Regards
Neil
On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe joerg.bru...@oracle.com
wrote:
Hi!
Neil Tompkins wrote:
Thanks for your reply. So should we create individual indexes on each
field or a multiple column index ??
This question cannot be answered without checking and measuring your
installation. The decision whether to create an index is always an act
of balancing:
- If there is an index, the database server can use it to find data
records by looking up the index, not scanning the base data.
This results in load reduction (both CPU and disk IO) and speeds up
query execution.
- If there is an index, the database server must maintain it whenever
data are altered (insert/update/delete), in addition to the base data.
This is increased load (both CPU and disk IO) and slows down data
changes.
So obviously you want to create only those indexes that are helpful for
query execution: you will never (voluntarily) create an index on a
column which isn't used in search conditions, or whose use is already
provided by other indexes.
Of the remaining candidate indexes, you will never (voluntarily) create
one that provides less gain in searches than it costs in data changes.
With MySQL, AFAIK there is the limitation that on one table only one
index can be used. As a result, the choice of indexes to create depends
on the searches executed by your commands, their relative frequency, and
the frequency of data changes.
To answer your other question: If you run aggregate functions (like
SUM(), MIN(), or MAX()) on all records of a table, their results could
be computed by accessing a matching index only. I don't know whether
MySQL does this, I propose you check that yourself using EXPLAIN.
If you run them on subsets of a table only, an index on that column will
not help in general.
In database implementations, there is the concept of a covering index:
If you have an index on columns A and B of some table, its contents
(without the base data) would suffice to answer
SELECT SUM(B) WHERE A = x
Again, I don't know whether MySQL does this, and I refer you to EXPLAIN.
HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com
ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603
This message contains confidential information and is intended only for
the individual named. If you are not the named addressee, you are notified
that reviewing, disseminating, disclosing, copying or distributing this
e-mail is strictly prohibited. Please notify the sender immediately by
e-mail if you have received this e-mail by mistake and delete this e-mail
from your system. E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore does not
accept liability for any loss or damage caused by viruses or errors or
omissions in the contents of this message, which arise as a result of e-mail
transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
94089, USA, FriendFinder.com
'players_bids', 'CREATE TABLE `players_bids` (
`players_bids_id` bigint(20) NOT NULL auto_increment,
`worlds_id` int(10) NOT NULL,
`seasons_id` int(10) NOT NULL,
`teams_id_from` bigint(20) NOT NULL,
`teams_id_to` bigint(20) NOT NULL,
`users_id_from` bigint(20) NOT NULL,
`users_id_to` bigint(20) NOT NULL,
`players_id` bigint(20) NOT NULL,
`bid_value` double NOT NULL default ''0'',
`bid_type` enum(''transfer'',''loan'',''exchange'') collate utf8_unicode_ci
NOT NULL,
`bid_status` varchar(45) collate utf8_unicode_ci NOT NULL,
`bid_date` datetime NOT NULL,
`bid_completed_date` datetime default NULL,
PRIMARY KEY (`players_bids_id`),
KEY `FK_players_bids_worlds_id` (`worlds_id`),
KEY `IDX_bid_date` (`bid_date`),
KEY `IDX_bid_status` (`bid_status`),
KEY `IDX_bid_type` (`bid_type`),
KEY
Design advice
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
GRANT issues
Greetings, I am attempting to set up permissions on DB with the following code from the terminal on Mac OS 10.6 logged into mysql with a user that has access to the mysql database GRANT SELECT ON pet_calendar.* TO username@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; I can get the user and password set up fine, but it does not grant any privileges. It is probably an easy fix that I am just missing, but I would appreciate your help. Thanks, -- Steve Marquez Marquez Design e-mail: smarq...@marquez-design.com web: http://www.marquez-design.com phone: 479-648-0325
Re: GRANT issues
Hi Steve Your statement will allow you to SELECT from any table in the pet_calendar database. You need to add INSERT, UPDATE, DELETE, CREATE etc to your GRANT statement. If you would like to have a super user, just GRANT ALL Mike - Original Message - From: Steve Marquez smarq...@marquez-design.com To: MySQL List mysql@lists.mysql.com Sent: Tuesday, October 05, 2010 10:50 AM Subject: GRANT issues Greetings, I am attempting to set up permissions on DB with the following code from the terminal on Mac OS 10.6 logged into mysql with a user that has access to the mysql database GRANT SELECT ON pet_calendar.* TO username@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; I can get the user and password set up fine, but it does not grant any privileges. It is probably an easy fix that I am just missing, but I would appreciate your help. Thanks, -- Steve Marquez Marquez Design e-mail: smarq...@marquez-design.com web: http://www.marquez-design.com phone: 479-648-0325 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: GRANT issues
hi Steve, after you login with the new account , you can use the command show grants; to check which permission had been already granted to this account as below. mysql show grants; +-+ | Grants for r...@localhost | +-+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +-+ 1 row in set (0.00 sec) best regards liuyann From: smarq...@marquez-design.com Subject: GRANT issues Date: Tue, 5 Oct 2010 09:50:54 -0500 To: mysql@lists.mysql.com Greetings, I am attempting to set up permissions on DB with the following code from the terminal on Mac OS 10.6 logged into mysql with a user that has access to the mysql database GRANT SELECT ON pet_calendar.* TO username@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; I can get the user and password set up fine, but it does not grant any privileges. It is probably an easy fix that I am just missing, but I would appreciate your help. Thanks, -- Steve Marquez Marquez Design e-mail: smarq...@marquez-design.com web: http://www.marquez-design.com phone: 479-648-0325 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Add record number to timestamped router data to facilitate cross join
On Fri, Oct 1, 2010 at 12:24 PM, Jake Peavy djstu...@gmail.com wrote: All, I have a number of routers which report in with various stats periodicially. This period is not a regular interval and can drift based on other factors. Each router drifts independently. The stats the routers provide need to be analyzed in terms of deltas between reports (rather than the absolute number). Therefore I need to perform a cross join to compare the rows for a given routerID (something like SELECT r1.timestamp, r2.counter1-r1.counter1 FROM router_data as r1 JOIN router_data as r2 ON (r2.recordNum = r1.recordNum + 1)) Here's an example of some raw data to give you an idea showing 3 records each from 2 devices: +--+--+--+--+--+ | routerID | timestamp| counter1 | counter2 | counter3 | +--+--+--+--+--+ |1 | 24/08/2010 10:36 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 36 | 75 | 31 | +--+--+--+--+--+ My plan, to facilitate the cross join, was to add a per-device record number like follows: +--+--+---+--+--+--+ | routerID | timestamp| recordNum | counter1 | counter2 | counter3 | +--+--+---+--+--+--+ |1 | 24/08/2010 10:36 | 1 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 1 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 2 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 2 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 3 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 3 | 36 | 75 | 31 | +--+--+---+--+--+--+ So here's my question, first, of course, is there a better way to perform the cross join? If not, what's the easiest way to add and increment the recordNum field? Can I do it directly in SQL somehow? Or do I need to do it in my parser? If I do it in my parser, it runs periodically (as it receives the reports) so I think it would need to figure out what record number it assigned to which device last so it would know where to restart the numbering. Should I hold that in the parser itself, or a separate table (SELECT routerID,last_used_record_num FROM last_used_record_nums; then parse, incrementing record num, then write the last ones back to that table) or by querying the main table itself (SELECT routerID, MAX(recordNum) FROM router_data GROUP BY routerID)? My only concern with the last approach is that router_data is going to get very large and that query may get very slow. TIA for any advice, Hey all, Anyone have any thoughts/advice on the best way to manage this record number? What about a stored procedure? Or use of variables? TIA -- -jp If you're traveling in a time machine, and you're eating corn on the cob, I don't think it's going to affect things one way or the other. But here's the point I'm trying to make: Corn on the cob is good, isn't it? deepthoughtsbyjackhandey.com
RE: Add record number to timestamped router data to facilitate cross join
I don't think I'd seriously consider the max() option, especially if you're expecting the table to grow large. Using a table to store the next record number (your last_used_record_nums table) is a technique that is fairly commonly used. You can increment and retrieve the value atomically if you use the LAST_INSERT_ID(): insert into last_used_record_nums(routerid, recordnum) values (, last_insert_id(1)) on duplicate key update recordnum = last_insert_id(recordnum + 1); then select last_insert_id(); to get the incremented value. You could probably wrap this in an INSERT trigger on your stats table so your application wouldn't need to worry about it. -Travis -Original Message- From: Jake Peavy [mailto:djstu...@gmail.com] Sent: Tuesday, October 05, 2010 10:34 AM To: MySQL General Mailing List Subject: Re: Add record number to timestamped router data to facilitate cross join On Fri, Oct 1, 2010 at 12:24 PM, Jake Peavy djstu...@gmail.com wrote: All, I have a number of routers which report in with various stats periodicially. This period is not a regular interval and can drift based on other factors. Each router drifts independently. The stats the routers provide need to be analyzed in terms of deltas between reports (rather than the absolute number). Therefore I need to perform a cross join to compare the rows for a given routerID (something like SELECT r1.timestamp, r2.counter1-r1.counter1 FROM router_data as r1 JOIN router_data as r2 ON (r2.recordNum = r1.recordNum + 1)) Here's an example of some raw data to give you an idea showing 3 records each from 2 devices: +--+--+--+--+--+ | routerID | timestamp| counter1 | counter2 | counter3 | +--+--+--+--+--+ |1 | 24/08/2010 10:36 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 36 | 75 | 31 | +--+--+--+--+--+ My plan, to facilitate the cross join, was to add a per-device record number like follows: +--+--+---+--+--+--+ | routerID | timestamp| recordNum | counter1 | counter2 | counter3 | +--+--+---+--+--+--+ |1 | 24/08/2010 10:36 | 1 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 1 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 2 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 2 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 3 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 3 | 36 | 75 | 31 | +--+--+---+--+--+--+ So here's my question, first, of course, is there a better way to perform the cross join? If not, what's the easiest way to add and increment the recordNum field? Can I do it directly in SQL somehow? Or do I need to do it in my parser? If I do it in my parser, it runs periodically (as it receives the reports) so I think it would need to figure out what record number it assigned to which device last so it would know where to restart the numbering. Should I hold that in the parser itself, or a separate table (SELECT routerID,last_used_record_num FROM last_used_record_nums; then parse, incrementing record num, then write the last ones back to that table) or by querying the main table itself (SELECT routerID, MAX(recordNum) FROM router_data GROUP BY routerID)? My only concern with the last approach is that router_data is going to get very large and that query may get very slow. TIA for any advice, Hey all, Anyone have any thoughts/advice on the best way to manage this record number? What about a stored procedure? Or use of variables? TIA -- -jp If you're traveling in a time machine, and you're eating corn on the cob, I don't think it's going to affect things one way or the other. But here's the point I'm trying to make: Corn on the cob is good, isn't it? deepthoughtsbyjackhandey.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: MySQL DB Version
How can we get our MySQL db upgraded to this version or greater ? You can also find these at: http://dev.mysql.com/downloads/ Cheers, Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
