Re: document for mysql performance improvement
Hi, If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. Or you can be logical about it and try to determine whether the IO performance is a symptom or a cause. If there are queries that don't have good indexes, add correct indexes is a smarter solution than add disks. Indeed, even the IO usage can be a red herring. I suggest a more systematic approach to the problem, such as Method R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Swap data in columns
Hi all. Sorry for very simple question, just can't figure out the solution. I need to swap data in column1 with data in column2. ++-+-+ | id | column1 | column2 | ++-+-+ | 1 | a | z | | 2 | b | y | | 3 | c | x | | 4 | d | w | | 5 | e | v | ++-+-+ Can you achieve this with a simple query? so for id 1 column1 = 'z' and column2 = 'a' and so on. Thanks guys, Igor
Re: document for mysql performance improvement
Can you show us the output of: show status like '%innodb%' JW On Tue, Sep 21, 2010 at 10:11 PM, vokern vok...@gmail.com wrote: And this is the innodb file size, does this matter for performance? $ du -h ibdata* 11G ibdata1 11G ibdata2 11G ibdata3 59G ibdata4 2010/9/22 vokern vok...@gmail.com: This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack= 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections= 800 query_cache_limit = 1M query_cache_size= 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.293.59 0.97 7.93 dm-0 0.00 0.000.560.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: Swap data in columns
I ran these commands:
use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
CHAR(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'),
('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
SELECT * FROM mydata;
I got this output:
lwdba@ (DB test) :: use test
Database changed
lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)
lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY
KEY,column1 CHAR(1),column2 CHAR(2));
Query OK, 0 rows affected (0.05 sec)
lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'),
('b','y'), ('c','x'), ('d','w'), ('e','v');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
5 rows in set (0.00 sec)
lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | z | a |
| 2 | y | b |
| 3 | x | c |
| 4 | w | d |
| 5 | v | e |
++-+-+
5 rows in set (0.00 sec)
GIVE IT A TRY !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: nixofortune [mailto:nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 12:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns
Hi all.
Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.
Thanks guys,
Igor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Swap data in columns
Couldn't you just rename the columns?
JW
On Wed, Sep 22, 2010 at 11:53 AM, Rolando Edwards
redwa...@logicworks.netwrote:
I ran these commands:
use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
CHAR(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'),
('c','x'), ('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
SELECT * FROM mydata;
I got this output:
lwdba@ (DB test) :: use test
Database changed
lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)
lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT
PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
Query OK, 0 rows affected (0.05 sec)
lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'),
('b','y'), ('c','x'), ('d','w'), ('e','v');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
5 rows in set (0.00 sec)
lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | z | a |
| 2 | y | b |
| 3 | x | c |
| 4 | w | d |
| 5 | v | e |
++-+-+
5 rows in set (0.00 sec)
GIVE IT A TRY !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: nixofortune [mailto:nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 12:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns
Hi all.
Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.
Thanks guys,
Igor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
-
Johnny Withers
601.209.4985
joh...@pixelated.net
Re: Swap data in columns
What about:
select `id`, `column1` as 'column2', `column2` as 'column1';
Steve
On Wed, 2010-09-22 at 13:06 -0500, Johnny Withers wrote:
Couldn't you just rename the columns?
JW
On Wed, Sep 22, 2010 at 11:53 AM, Rolando Edwards
redwa...@logicworks.netwrote:
I ran these commands:
use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
CHAR(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'),
('c','x'), ('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
SELECT * FROM mydata;
I got this output:
lwdba@ (DB test) :: use test
Database changed
lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)
lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT
PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
Query OK, 0 rows affected (0.05 sec)
lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'),
('b','y'), ('c','x'), ('d','w'), ('e','v');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
5 rows in set (0.00 sec)
lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | z | a |
| 2 | y | b |
| 3 | x | c |
| 4 | w | d |
| 5 | v | e |
++-+-+
5 rows in set (0.00 sec)
GIVE IT A TRY !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: nixofortune [mailto:nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 12:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns
Hi all.
Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.
Thanks guys,
Igor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.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: Swap data in columns
Hi Rolando,
This is perfect solution I was looking for.
Why do you use left join here? It looks like inner join works fine as well.
Thanks.
Rolando Edwards wrote:
I ran these commands:
use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
CHAR(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'),
('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
SELECT * FROM mydata;
I got this output:
lwdba@ (DB test) :: use test
Database changed
lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)
lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT
PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
Query OK, 0 rows affected (0.05 sec)
lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'),
('b','y'), ('c','x'), ('d','w'), ('e','v');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
5 rows in set (0.00 sec)
lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | z | a |
| 2 | y | b |
| 3 | x | c |
| 4 | w | d |
| 5 | v | e |
++-+-+
5 rows in set (0.00 sec)
GIVE IT A TRY !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
AIM Skype :
rolandologicworxredwa...@logicworks.nethttp://www.linkedin.com/in/rolandoedwards
-Original Message-
From: nixofortune [mailto:nixofort...@googlemail.com
nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 12:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns
Hi all.
Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.
Thanks guys,
Igor
Re: Swap data in columns
Hi Rolando,
This is perfect solution I was looking for.
Why do you use left join here? It looks like inner join works fine as well.
Thanks.
Rolando Edwards wrote:
I ran these commands:
use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
CHAR(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'),
('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
SELECT * FROM mydata;
I got this output:
lwdba@ (DB test) :: use test
Database changed
lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)
lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY
KEY,column1 CHAR(1),column2 CHAR(2));
Query OK, 0 rows affected (0.05 sec)
lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'),
('b','y'), ('c','x'), ('d','w'), ('e','v');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
5 rows in set (0.00 sec)
lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | z | a |
| 2 | y | b |
| 3 | x | c |
| 4 | w | d |
| 5 | v | e |
++-+-+
5 rows in set (0.00 sec)
GIVE IT A TRY !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: nixofortune [mailto:nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 12:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns
Hi all.
Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.
Thanks guys,
Igor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Swap data in columns
This is even better!
JW
On Wed, Sep 22, 2010 at 2:27 PM, Steve Staples sstap...@mnsi.net wrote:
What about:
select `id`, `column1` as 'column2', `column2` as 'column1';
Steve
On Wed, 2010-09-22 at 13:06 -0500, Johnny Withers wrote:
Couldn't you just rename the columns?
JW
On Wed, Sep 22, 2010 at 11:53 AM, Rolando Edwards
redwa...@logicworks.netwrote:
I ran these commands:
use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
CHAR(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'),
('c','x'), ('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
SELECT * FROM mydata;
I got this output:
lwdba@ (DB test) :: use test
Database changed
lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)
lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL
AUTO_INCREMENT
PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
Query OK, 0 rows affected (0.05 sec)
lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES
('a','z'),
('b','y'), ('c','x'), ('d','w'), ('e','v');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
5 rows in set (0.00 sec)
lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | z | a |
| 2 | y | b |
| 3 | x | c |
| 4 | w | d |
| 5 | v | e |
++-+-+
5 rows in set (0.00 sec)
GIVE IT A TRY !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: nixofortune [mailto:nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 12:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns
Hi all.
Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.
Thanks guys,
Igor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
-
Johnny Withers
601.209.4985
joh...@pixelated.net
RE: Swap data in columns
Oh yea, INNER JOIN is cleaner to use
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: Egor Shevtsov [mailto:nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 4:14 PM
To: MySQL mailing list
Subject: Re: Swap data in columns
Hi Rolando,
This is perfect solution I was looking for.
Why do you use left join here? It looks like inner join works fine as well.
Thanks.
Rolando Edwards wrote:
I ran these commands:
use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
CHAR(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'),
('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
SELECT * FROM mydata;
I got this output:
lwdba@ (DB test) :: use test
Database changed
lwdba@ (DB test) :: DROP TABLE IF EXISTS mydata;
Query OK, 0 rows affected (0.00 sec)
lwdba@ (DB test) :: CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT
PRIMARY KEY,column1 CHAR(1),column2 CHAR(2));
Query OK, 0 rows affected (0.05 sec)
lwdba@ (DB test) :: INSERT INTO mydata (column1,column2) VALUES ('a','z'),
('b','y'), ('c','x'), ('d','w'), ('e','v');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
5 rows in set (0.00 sec)
lwdba@ (DB test) :: UPDATE mydata A LEFT JOIN mydata B USING (id) SET
A.column1=B.column2,A.column2=B.column1;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
lwdba@ (DB test) :: SELECT * FROM mydata;
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | z | a |
| 2 | y | b |
| 3 | x | c |
| 4 | w | d |
| 5 | v | e |
++-+-+
5 rows in set (0.00 sec)
GIVE IT A TRY !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: nixofortune [mailto:nixofort...@googlemail.com]
Sent: Wednesday, September 22, 2010 12:30 PM
To: mysql@lists.mysql.com
Subject: Swap data in columns
Hi all.
Sorry for very simple question, just can't figure out the solution.
I need to swap data in column1 with data in column2.
++-+-+
| id | column1 | column2 |
++-+-+
| 1 | a | z |
| 2 | b | y |
| 3 | c | x |
| 4 | d | w |
| 5 | e | v |
++-+-+
Can you achieve this with a simple query?
so for id 1 column1 = 'z' and column2 = 'a' and so on.
Thanks guys,
Igor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.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: There is a ram limit?
On Fri, Jul 9, 2010 at 12:03 AM, Johan De Meersman vegiv...@tuxera.be wrote: This will mostly depend on your OS, really. Assuming you're running a 64-bit flavour of *nix on that box, I don't think you have to worry. Linux on 64-bits. The default installation will use all the ram it needs or do I have to configure something else? On Fri, Jul 9, 2010 at 4:44 AM, Camilo Uribe camilo.ur...@gmail.com wrote: Hi: There is a limit in the amount of ram I could use for mysql? (I have a server with 96GB of ram) -- 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=arch...@jab.org
Re: Swap data in columns
update mydata set column1 = column2, column2 = column1 (works in sqlserver, can't try mysql at the moment) You can select which rows by adding a where clause obviously. I suppose that the field values are copied to a buffer which is the written to the table at the end of the update (or row by row?) - Original Message - From: nixofortune nixofort...@googlemail.com To: mysql@lists.mysql.com Sent: Wednesday, September 22, 2010 5:29 PM Subject: Swap data in columns Hi all. Sorry for very simple question, just can't figure out the solution. I need to swap data in column1 with data in column2. ++-+-+ | id | column1 | column2 | ++-+-+ | 1 | a | z | | 2 | b | y | | 3 | c | x | | 4 | d | w | | 5 | e | v | ++-+-+ Can you achieve this with a simple query? so for id 1 column1 = 'z' and column2 = 'a' and so on. Thanks guys, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
2010/9/23 Johnny Withers joh...@pixelated.net Can you show us the output of: show status like '%innodb%' JW Sure. mysql show status like '%innodb%'; +---++ | Variable_name | Value | +---++ | Innodb_buffer_pool_pages_data | 262143 | | Innodb_buffer_pool_pages_dirty| 7219 | | Innodb_buffer_pool_pages_flushed | 376090524 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total| 262144 | | Innodb_buffer_pool_read_ahead_rnd | 385466 | | Innodb_buffer_pool_read_ahead_seq | 1304599| | Innodb_buffer_pool_read_requests | 19253892075| | Innodb_buffer_pool_reads | 142749467 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 3491971805 | | Innodb_data_fsyncs| 32809939 | | Innodb_data_pending_fsyncs| 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes| 0 | | Innodb_data_read | 4013196644352 | | Innodb_data_reads | 147753642 | | Innodb_data_writes| 440467519 | | Innodb_data_written | 12643997136896 | | Innodb_dblwr_pages_written| 376090524 | | Innodb_dblwr_writes | 5464581| | Innodb_log_waits | 6599 | | Innodb_log_write_requests | 490350909 | | Innodb_log_writes | 201315186 | | Innodb_os_log_fsyncs | 13605257 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 319623115776 | | Innodb_page_size | 16384 | | Innodb_pages_created | 6050545| | Innodb_pages_read | 244945432 | | Innodb_pages_written | 376090524 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 594325 | | Innodb_row_lock_time_avg | 154| | Innodb_row_lock_time_max | 27414 | | Innodb_row_lock_waits | 3857 | | Innodb_rows_deleted | 2170086| | Innodb_rows_inserted | 550876090 | | Innodb_rows_read | 15529216710| | Innodb_rows_updated | 142880071 | +---++ 42 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
