Performing subtraction between fields
Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performing subtraction between fields
On 21/08/2010 07:25, b...@qxhp.com wrote: Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Given two variables, $a and $b: INSERT INTO mytable SET A = $a, B = $b, C = $a - $b or INSERT INTO mytable (A, B, C) VALUES ($a, $b, $a - $b) or, if you've previously inserted A and B: UPDATE mytable SET C = A - B http://dev.mysql.com/doc/refman/5.1/en/numeric-functions.html Simples :-) Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performing subtraction between fields
Do you have a really good reason to store a computed value? It's only useful if you will perform a search on the column, else you could just do the subtraction when you SELECT columns A and B. - Ashish On Sat, Aug 21, 2010 at 11:55 AM, b...@qxhp.com wrote: Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ashish.mukher...@gmail.com
Re: Performing subtraction between fields
Ashish, Mark and off-list responders, Thanks! Ashish, is there a really good general reason not to store a computed value? Searches will be done on this column. Searches like 'Please send me the rows with the ten highest values of C.' Do you have a really good reason to store a computed value? It's only useful if you will perform a search on the column, else you could just do the subtraction when you SELECT columns A and B. - Ashish On Sat, Aug 21, 2010 at 11:55 AM, b...@qxhp.com wrote: Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ashish.mukher...@gmail.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: Performing subtraction between fields
Well, since you are searching that column, it's probably a good idea. Possible scenarios for not storing computed values - 1) Data integrity - say, the columns A and B are updated but C does not get updated, resulting in an anomalous situation 2) Data-set is large and the extra column leads to additional bloat and you are not searching the column C. Then, selectivity is also a factor. Best way is to do a EXPLAIN for your query and see the gain you get from having the additional column vs not. It's hard to say what's right for you without knowing more about the data-set. Regards, Ashish On Sat, Aug 21, 2010 at 4:32 PM, Ashley Stars b...@qxhp.com wrote: Ashish, Mark and off-list responders, Thanks! Ashish, is there a really good general reason not to store a computed value? Searches will be done on this column. Searches like 'Please send me the rows with the ten highest values of C.' Do you have a really good reason to store a computed value? It's only useful if you will perform a search on the column, else you could just do the subtraction when you SELECT columns A and B. - Ashish On Sat, Aug 21, 2010 at 11:55 AM, b...@qxhp.com wrote: Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ashish.mukher...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ashish.mukher...@gmail.com
Re: Responsibilities of the main mysqld thread?
Couple of thoughts ... lsof -p pid of mysql process may give some clue of what files/tables it's reading/writing. From that you may be able to deduce something useful. Check the slow query log and see if any of the queries are CPU-intensive (by doing an EXPLAIN) and are the bottleneck. - Ashish On Fri, Aug 20, 2010 at 10:09 PM, Tom Kleinpeter tomkleinpe...@gmail.comwrote: I've been watching our mysqld procs in htop and the root thread is using 75-100% of a CPU most of the time. I'm trying to understand what that is being used for, and so I was hoping someone could tell me what that thread does that might be using a lot of CPU. We are using 5.5.4-m3, we use MyISAM tables with full text indexes, do around 1500-2000 queries per second, accept around 150 connections per second, and at the moment both our key cache and our query cache are disabled (I was curious if that contention was an issue). CPU usage didn't really change with the caches disabled. The server is doing around 2 megabytes of traffic per second. We store a bunch of data compressed, and use uncompress(columnName) in queries to get it back out. None of these things set off any red flags for me, but it has been a number of years since I've tuned MySQL, so I don't really trust myself. Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ashish.mukher...@gmail.com
Re: Performing subtraction between fields
If you want C to always be A-B then it would, in my opinion, be a very bad idea to store C in the table. Instead you can just put C in your query. SELECT `A`, `B`, `A` - `B` AS `C` FROM `table` If that seems like a hassle, you could always create a view using that select. Chris W b...@qxhp.com wrote: Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: query help
Something like this might work: insert into domains select a.accountid, reverse(a.domainid), a.mailname from domains a left outer join domains b on b.domainid = reverse(a.domainid) and b.accountid = a.accountid and b.mailname = a.mailname where b.domainid is null; -Travis -- From: Steven Buehler st...@ibushost.com Sent: Friday, August 20, 2010 3:30 PM To: mysql@lists.mysql.com Subject: query help I am hoping that I can do this with one query, I have a table, Domains with 3 columns accountID, domainID, mailname what I am trying to do is find all accountID's for domainID of 12345 and see if a second row with domainID of 54321 exists for that accountID,mailname. If it doesn't exist, I want it to insert another row with the same accountID and mailname, but with the second (54321) domainid. Any help would be appreciated. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow ALTER TABLE on 70M row InnoDB table
Most alter table operations in 5.0 will rebuild the entire table. The best thing to increase for alter table speed in innodb is the buffer pool. For more details on how innodb handles alter table see http://ebergen.net/wordpress/2007/05/07/how-alter-table-locks-tables-and-handles-transactions/ On Wednesday, August 18, 2010, Xn Nooby xno...@gmail.com wrote: From what I have read, ALTER TABLE to add an index causes the entire table to be duplicated, so wouldn't my ALTER TABLE command be duplicating the work done by the SELECT command? On Wed, Aug 18, 2010 at 4:50 PM, mos mo...@fastmail.fm wrote: At 02:52 PM 8/18/2010, Xn Nooby wrote: Below is a generic version of the code I am trying. It does copy the rows very quickly, but I will have to test to see how quickly the indices are built. Is the below code what you were suggesting? I had a little trouble dropping and later adding the primary index, but I think I got it figured out. Below I basically do this: make the_table_clone from the the_table drop the indices on the_table_clone copy the row from the_table to the_table_clone add the indices back to the_table_clone If this runs fast enough, I will then drop the_table, and rename the_table_clone to the_table USE the_database; DROP TABLE IF EXISTS the_table_clone; CREATE TABLE the_table_clone LIKE the_table; Or you can try something like: create table the_table_clone engine=innodb select * from the_table limit=0; This will create the same table structure but not the indexes so you don't have to drop the indexes below. # drop minor indices on clone ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col2_col3; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col4_col6; ALTER TABLE the_table_clone DROP INDEX IX_the_table_on_col5_col2; # drop primary index on clone ALTER TABLE the_table_clone CHANGE id id INT UNSIGNED; You still need the statement above to change the autoinc to integer if you use my Create Table... statement above. ALTER TABLE the_table_clone DROP PRIMARY KEY; # add 2 new columns to clone ALTER TABLE the_table_clone ADD flag_field int(11) DEFAULT '0', ADD price_amount float DEFAULT '0'; # copy rows INSERT the_table_clone SELECT *, NULL, NULL FROM the_table LIMIT 0,1000; #INSERT the_table_clone SELECT *, NULL, NULL FROM the_table; Why do you have two insert statements? If you are inserting a group of records at a time then you need a limit statement on each, and increment the offset by the number of rows that have been added. I would explicitly specify the column list for both the Insert and the Select to make sure they match up. There is no point going through all this if it inserts the data into the wrong columns! Check the data before creating the indexes to make sure the same number of rows have been copied over and the data is in the correct columns. # Add back indices in one command (for max speed) ALTER TABLE the_table_clone \ ADD INDEX IX_the_table_on_col2_col3 (col2,col3),\ ADD INDEX IX_the_table_on_col4_col6 (col4,col6),\ ADD INDEX IX_the_table_on_col5_col2 (col5,col2),\ MODIFY id INT SIGNED AUTO_INCREMENT,\ ADD PRIMARY KEY(col1); Correct. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performing subtraction between fields
In the last episode (Aug 21), Ashish Mukherjee said: Well, since you are searching that column, it's probably a good idea. Possible scenarios for not storing computed values - 1) Data integrity - say, the columns A and B are updated but C does not get updated, resulting in an anomalous situation You could use a trigger that updates C whenever A or B changes, to make sure it stays in synch. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
