- Original Message -
> From: "mos"
>
> If you could use MyISAM tables then you could use Merge Tables and
Ick, merge tables :-) If your version is recent enough (Isn't 4.whatever long
out of support anyway?) you're much better off using partitioning - it's
engine-agnostic and has a lot
If you could use MyISAM tables then you could use Merge Tables and
create a table for each day (or whatever period you are collecting
data for). Then when it is time to get rid of the old data, drop the
oldest table (T2001 or T10 for 10 days ago) and create a new
empty table for the new day
Excellent point... replication makes many things trikier
On 11/4/11 9:54 AM, Derek Downey wrote:
Be careful deleting with limit. If you're replicating, you're not guaranteed
the same order
> of those you've deleted.
Perhaps a better way to delete in smaller chunks is to increase the id valu
Be careful deleting with limit. If you're replicating, you're not guaranteed
the same order of those you've deleted.
Perhaps a better way to delete in smaller chunks is to increase the id value:
DELETE FROM my_big_table WHERE id> 5000;
DELETE FROM my_big_table WHERE id> 4000;
etc
-- Derek
On
I've had some luck in the past under similar restrictions deleting in
chunks:
delete from my_big_table where id > 2474 limit 1000
But really, the best way is to buy some more disk space and use the
new table method
On 11/4/11 1:44 AM, Adarsh Sharma wrote:
Thanks Anand,
Ananda Kumar wrote:
W
- Original Message -
> From: "Reindl Harald"
>
> well i guess you have to sit out add the key
> wrong table design having an id-column without a key or
> something weird in the application not using the primary
> key for such operations
For high-volume insert-only tables the lack of a ke
PLEASE do not top-post after you got a reply
at the bottom of your quote
sorry, but i can not help you with your application
if it for whatever reason uses the filed 'id' in a where-statement
and your table has no key on this column your table-design is
wrong and you have to add the key
yes this
Create PROCEDURE qrtz_purge() BEGIN
declare l_id bigint(20);
declare NO_DATA INT DEFAULT 0;
DECLARE LST_CUR CURSOR FOR select id from table_name where id> 123;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA = -1;
OPEN LST_CUR;
SET NO_DATA = 0;
FETCH LST_CUR INTO l_id;
WH
Am 04.11.2011 08:22, schrieb Adarsh Sharma:
> delete from metadata where id>2474;
> but it takes hours to complete.
>
> CREATE TABLE `metadata` (
> `meta_id` bigint(20) NOT NULL AUTO_INCREMENT,
> `id` bigint(20) DEFAULT NULL,
> `url` varchar(800) DEFAULT NULL,
> `meta_field` varchar(200) DEF
Thanks Anand,
Ananda Kumar wrote:
Why dont you create a new table where id < 2474,
rename the original table to "_old" and the new table to actual table
name.
I need to delete rows from 5 tables each > 50 GB , & I don't have
sufficient space to store extra data.
My application loads 2 GB dat
Why dont you create a new table where id < 2474,
rename the original table to "_old" and the new table to actual table name.
or
You need to write a stored proc to loop through rows and delete, which will
be faster.
Doing just a simple "delete" statement, for deleting huge data will take
ages.
re
Dear all,
Today I need to delete some records in > 70 GB tables.
I have 4 tables in mysql database.
my delete command is :-
delete from metadata where id>2474;
but it takes hours to complete.
One of my table structure is as :-
CREATE TABLE `metadata` (
`meta_id` bigint(20) NOT NULL AUTO_IN
use event scheduler.
On Mon, Aug 1, 2011 at 12:00 PM, hezjing wrote:
> Hi
>
> I want to delete the records which are older than two hours from a table.
>
> Currently, I have scheduled a cron job script to delete the records every
> one hour. I'm wondering if there is a more elegant way of doing
Hi
I want to delete the records which are older than two hours from a table.
Currently, I have scheduled a cron job script to delete the records every
one hour. I'm wondering if there is a more elegant way of doing this with
out the cron job script?
--
Hez
Harald Fuchs wrote:
In article <[EMAIL PROTECTED]>,
Ross Honniball <[EMAIL PROTECTED]> writes:
Hi all,
I have positively identified the row I want to delete using:
'SELECT * FROM table LIMIT 10,1'
No, you didn't. Since you did not include an ORDER BY clause, MySQL
has returned the tenth row acco
the where clause!
Mike
> -Original Message-
> From: Michael Pheasant [mailto:[EMAIL PROTECTED]
> Sent: Sunday, 4 April 2004 5:05 PM
> To: [EMAIL PROTECTED]
> Subject: RE: Deleting records using the 'LIMIT' clause
>
>
> Hi,
>
> You need to dele
by select.
Cheers,
M
> -Original Message-
> From: Ross Honniball [mailto:[EMAIL PROTECTED]
> Sent: Sunday, 4 April 2004 10:40 AM
> To: [EMAIL PROTECTED]
> Subject: Deleting records using the 'LIMIT' clause
>
>
> Hi all,
>
> I have positively identi
Hi all,
I have positively identified the row I want to delete using:
'SELECT * FROM table LIMIT 10,1'
This has returned 1 record and I now want to DELETE the record.
How do I identify this record in my DELETE statement?
(using 'DELETE FROM table LIMIT 10,1' does not work)
NOTE : I can't ident
RE: Howto reduce size of MYISAM files after deleting records?
Shutdown the mysql server
Goto the datadir (/var/lib/mysql)
Goto the db directory
Type
myisamchk -r -S -a *.MYI this will reclaim the bad blocks caused by the
delete.
- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for F
On 28 Oct 2003 at 13:59, Dan Greene wrote:
> Is there a way to do this on a live running (i.e. production) server?
http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html
--
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list
go Sineiro; MySql Mail List
-->Subject: RE: Howto reduce size of MYISAM files after deleting
records?
-->
-->Is there a way to do this on a live running (i.e. production) server?
-->
-->> -Original Message-
-->> From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
--&
o reduce size of MYISAM files after deleting records?
>
>
> Shutdown the mysql server
> Goto the datadir (/var/lib/mysql)
>
> Goto the db directory
> Type
> myisamchk -r -S -a *.MYI this will reclaim the bad blocks
> caused by the
> delete.
>
>
>
> - Da
-->-Original Message-
-->From: Iago Sineiro [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, October 28, 2003 9:52 AM
-->To: MySql Mail List
-->Subject: Howto reduce size of MYISAM files after deleting records?
-->
-->Hi.
-->
-->I delete a lot of records of one MyISAM table
Hi.
I delete a lot of records of one MyISAM table in MySQL 4.0.16. After that I
execute mysqladmin refresh and now the size of the files of the table is the
same than before.
Is something wrong or is necessary doing something more to reduce the size
of the files?
Thanks in advance.
Iago.
--
We have a script that deletes records from a database that are over then
a certain date. We had no problem running the delete on 3.x, but on
4.0.14, we are having a problem.
Here is the command that does the delete:
$EXECPATH/mysql --host=hostname --user=blah --password=blah --execute="delete fr
ExpiryDate
On 28-Feb-2003 Jeff Snoxell wrote:
> Hi,
>
> If I run a mysql query then work through the results of that query one at a
> time, deleting the record sometimes eg:
>
> while (my $href = $sth->fetchrow_hashref())
> {
>if ($href->{'ExpiryDate'} eq '2003-02-22 00:00:00') {
> # Do
Hi,
If I run a mysql query then work through the results of that query one at a
time, deleting the record sometimes eg:
while (my $href = $sth->fetchrow_hashref())
{
if ($href->{'ExpiryDate'} eq '2003-02-22 00:00:00') {
# Do something
# then...
$db->do("DELETE FROM MyTable WHERE ID
On Wednesday 22 January 2003 19:39, Bill Rausch wrote:
> I've inherited a busted database and need to clean it up. I can't
> figure out how to do something which seemingly should be simple.
>
> For example, I've got two tables:
>
> Table One
> id int primary key auto_increment not null
> ...other
Hi,
I've inherited a busted database and need to clean it up. I can't
figure out how to do something which seemingly should be simple.
For example, I've got two tables:
Table One
id int primary key auto_increment not null
...other data
Table Two
id int primary key auto_increment not null
...o
> Is there a way to delete records in a table by specifying row numbers.
for
> example:
>
> delete from tbl1 where row > 900 and row <1000;
How do you get hold of the row numbers? The rows in the database are NOT
the same as the rows in
any particular SELECT you may have done. How the database s
Is there a way to delete records in a table by specifying row numbers. for
example:
delete from tbl1 where row > 900 and row <1000;
Thanks
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
h
31 matches
Mail list logo
