Tagged Questions

0
votes
1answer
32 views

Need ideas about OPTIMIZE TABLE

Looking at a database with 10 tables and fairly active at by-the-hour changes. On the first of each month, I purge some rows from 3 tables to remove outdated material and keep the size down. All of ...
1
vote
1answer
64 views

Delete entry from related tables

I can not use InnoDB engine because of some dependency troubles in LuaSQL and PtokaX. My current query is something like: DELETE e.*, m.* FROM `entries` e INNER JOIN `magnets` m ON ...
2
votes
4answers
131 views

Delete the N oldest entries grouped by multiple columns

I´d like to delete the oldest entries of my database, but in each category shall remain at least N words of each char. Structure of TABLE words id | word | char | category For example I have the ...
1
vote
1answer
48 views

Will deleting data in master automatically delete data in slave?

I have data replication setup on MySQL. If I delete data on the master, will replication know data was deleted and automatically delete the same data on slave?
0
votes
2answers
86 views

Fastest way to delete matching rows in MySQL?

I have more than 10 million records in a table. I want to delete the records where the email column contains .ac.. What is the fastest method to perform the task? Table Schema CREATE TABLE `master` ...
1
vote
1answer
156 views

mysql query - how to delete duplicates based on highest int?

I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId. ...
1
vote
1answer
54 views

Mysql setting a record as deleted or archive

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is ...
3
votes
2answers
145 views

Can I do anything about a very long running KILL command?

I've got a table with 103M+ records in it and takes up 70GB on the disk. I decided to DELETE (in one statement) 70% of the records. As expected, this took an awfully long time to run. I decided to ...
0
votes
1answer
74 views

myISAM data loss at DELETE / INSERT

I have a linked table id_group | id_user. Every time I add a one or more users to a group I DELETE all the links and INSERT the users again including the new one(s). This is how the framework of the ...
4
votes
1answer
1k views

Why is DELETE so much slower than SELECT, then DELETE by id?

I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
1
vote
2answers
1k views

remove duplicate rows in mysql table that does not contain primary key

I have a table item having just one column name:- name ----- toys shirt mobile Shirt speaker Toys .... .... I am trying to delete duplicate rows except one duplicated record with the help of below ...
3
votes
3answers
648 views

Optimizing DELETE Query on MySQL MEMORY Table

I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. ...
1
vote
2answers
79 views

How do automaticly delete rows from other tables

I have a MySql DB. I have a table - recipes. Also have 2 tables -ingredients and instructions. both have for each entry - a foreign key to the related recipe. I want that when I delete an entry from ...
1
vote
2answers
1k views

MySQL: Delete all but last N records

Consider the following table: mysql> DESCRIBE pixels; +---------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default ...
2
votes
2answers
107 views

How can I delete repeated rows?

I have a system that gathers RSS from many sites including UTF-8 based sites (non-english sites). I have tried to avoid repeated rows but I have repeats in my table. RSS_items (id(auto_increment) ...
1
vote
1answer
291 views

Delete by primary key takes too long

What would make a query that deletes a row using the primary key of the table .. take too long ? This is a table with about 1.4M rows that gets .. 70%/30% read/write . I don't know where to look. ...
1
vote
2answers
1k views

MySQL - automatically update all references to specific table to use on delete cascade?

I have a specific table for one of my software packages on a MySQL 5.1 box which I need to drop a few hundred entries. These are user names with names in uppercase, the application is case specific ...
3
votes
2answers
3k views

What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-) I ...
2
votes
2answers
67 views

Delete all highscores from a certain country

I have two tables: tbl_players tbl_highscores I need to delete all highscores that are registered from a certain country. My problem is that the language are set per player, rather than by ...
4
votes
2answers
395 views

How to implement soft deletes?

What is your method of implementing soft deletes in a database? Ideally the solution would allow for decent performance on large tables take relationships into account take unique keys into account ...
6
votes
3answers
1k views

What is the best approach to run a DELETE query on 92K of 4 million rows?

Options that have occurred to me: One big delete statement. For example: delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b') Same as above, but chunk in smaller pieces, 10k at a ...
7
votes
3answers
603 views

Deleting all duplicates

I am trying to delete all the duplicates but keeping single record only (shorter id). Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones. ...
2
votes
3answers
2k views

Clear all tables with one DELETE

Is it possible to clear all tables with one request? I have tried the following with no result. DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as ...