transfer huge mysql db between partitions
I have a similar question. I've run out of disk space on one of my partitions. What would be the best way to move one table over to another partition? I see you can specify DATA DIRECTORY and INDEX DIRECTORY, but when I try and ALTER or CREATE LIKE with these parameters, the table stays where it is on the original partition. Also, the table is a few Gb, so the quickest way possible would be best :) Any advice much appreciated! Helen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding columns to a large table
Thanks for all the help on this one. Its a MyISAM table. I ended up creating a temp table and selecting the data in... took 2 mins. I feel totally blonde for not thinking of that earlier! Thanks for being there, everyone! Helen - Original Message - From: praj To: Helen M Hudson Cc: MySQL List Sent: Monday, September 25, 2006 1:23 PM Subject: Re: adding columns to a large table There must be some thing went wrong with your settings in my.cnf file.For an 3 million records 24 hrs is bad :( myisam_sort_buffer_size value in my.cnf ? Also you can apporx check the status by doing file size check on the table in database dir and the temoprory table created in the data base dir . -- Praj Helen M Hudson wrote: let you know shortly or I have a table with about 3million rows and I'm having trouble altering it to add 4 more columns. I'm using the MySQL Administrator and I've left it more than 24hours and its still not finished. I was going to try using a login to the console next, but is there a better way? Any advice much appreciated! Helen
adding columns to a large table
I have a table with about 3million rows and I'm having trouble altering it to add 4 more columns. I'm using the MySQL Administrator and I've left it more than 24hours and its still not finished. I was going to try using a login to the console next, but is there a better way? Any advice much appreciated! Helen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
changing datadir
Has anyone good advice on changing the datadir on a Linux box. I have version 5.0.16 and my databases amount to about 5Gb. I'd like to move them over to one of the other logical disks on the Linux file system. Without any advice, I'd dump, reconfigure the datadir line in the cfg, and restore... but if anyone has a better way, please let me know! Helen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
quickie for a non-blonde!
If I have a table primary key id | code| name 1 | ABC | company 1 name 2 | ABC | company 1 name 3 | ABC | new company 1 name 4 | ABC | new company 1 name 5 | DEF| company 2 name 6 | DEF| company 2 name 7 | DEF| new company 2 name 8 | DEF| new company 2 name (the company is changing name every now and then and i need to extract their latest name) how can i get back 4 | ABC | new company 1 name 8 | DEF| new company 2 name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a tricky join
Hi all I'm gradually learning how much simpler it is to do things with joins. I can tell that I haven't seen the light yet... but I'm expecting fireworks pretty soon when it all falls into place brain-wise and I can wallow in the joy of smaller more efficient sql! I'd really appreciate a little help with this one: I have an order database containing multiple rows for each order. The problem is that if there is further activity on an order in future days, the system feeding me resends the whole order again. So I'd like a way of excluding all previous instances of an order when I'm doing calculations. So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 I know its a one-liner for someone who has reached the light... so any help very much appreciated! Helen
Re: a tricky join
Yes, I can see how this would work for just the one order and hardcoding the 100... but I cannot assume only to sum distinct values and my table has other order_refs in it with the same multiple rows of over multiple days, so I need a more generic select that will list this nice summary for all orders... do you see what I mean? e.g. id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude for order 100 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 6 | 2/1/01 | 101 | 1 i also need to exclude these 2 rows 7 | 2/1/01 | 101 | 2000 out of the calculation for order 101 8 | 2/1/01 | 101 | 1 9 | 3/1/01 | 101 | 2000 10 | 3/1/01 | 101 | 500 and I want to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 3/1/01 | 101 | 12500 Helen Quoting Helen M Hudson [EMAIL PROTECTED]: So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 Not tested SELECT MAX(Date), order_ref, SUM(amount) FROM table WHERE order_ref=100 GROUP BY amount That should sum all the amounts that is distinct, and have a order ref of 100. I'm not sure if MAX(date) will be accepted - but there are better ways to select the date depending on the column type.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
