Delete All Records In A Table Takes Forever -- Anyone Know Why?
I have a table called table_a that has 1 record in it. I delete that record, which because of foreign keys (the tables are type InnoDB) will cause the records in 8 other tables to be deleted.
The 8 other tables have a maximum of 200,000 records in them. 2 of them have that many while the remaining have < 5,000 records.
Currently, deleting that 1 record has taken hours upon hours with no end in site. Does anyone know why in the world this would take so long? Is there any way to speed it up (maybe a config setting I am missing or something)?
View Complete Forum Thread with Replies
Related Forum Messages:
ORDER BY Takes Forever
I'm having problems with ORDER BY. When I run a select targeting just one of my tables, it runs quickly and nicely. When doing the following: select * from rubbet, kommun where ortnamn like '%Johannes%' and rubbet.harad=kommun.harad and rubbet.socken=kommun.socken order by rubbet.ortnamn; it screws up bad. The query takes about 2 minutes to run before showing the result: "74 rows fetched in 0.0032s (112.5129s)" obviously the query takes no longer than 0.0032s but the ORDER BY clause makes it take forever to bring back the result. without the order by the whole thing runs very smoothly.
View Replies !
Subquery Takes Forever
I have two problems where I'm trying to retrieve data using a query. Part 1: I am doing a simple sub query which is on a two data sets with no more than 3000 rows in total. The query is taking 28 seconds to execute? Why I don't have a clue? Quote: 'Select Title FROM products WHERE Product_ID IN (SELECT Product_ID FROM order_items WHERE Order_ID="'.$id.'")' Part 2: When I have solved the above I'm looking to do a join, but don't know how to approach it ? Basically I'm trying to get a complete data set for a product/order like the following: SELECT * (which includes product_id) from items AND SELECT Title from products where product_id =(the product_id is retrieved from items); // using a join
View Replies !
MySQL Takes FOREVER To Start Back Up
Need some help here. I am running MySQL 4.1 on a windows 2003 server. We've had the server for about a year and right now the data file for the database is up to 345mb. Its growing week by week. Lately if I had to restart my server once windows comes back up it takes 20min for MySQL to start back up.
View Replies !
Delete Corresponding Records
I'm messing around with this query but for some reason it's not working. DELETE FROM zsmos_comprofiler c, zsmos_users u, zsmos_core_acl_aro a, zsmos_core_acl_groups_aro_map m WHERE c.id=u.id AND c.id=a.value AND m.aro_id=a.aro_id AND ((DATE_SUB(CURDATE(),INTERVAL 60 DAY) > u.registerDate)&& (u.lastvisitDate='0000-00-00 00:00:00')) AND c.confirmed = '0' I want to delete corresponding records in 4 tables linked as above - I've tested it as a SELECT query and that works ok - when I change it to a delete though it throws up an error.
View Replies !
All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in MySQlL 5. In My first select statement I get all my records from Table B SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` In my second statement I get my records that match in this case phase 401 in Table B and all my Table A records for phase 401. SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` Where table_A.PHASE In ('401' ) Now I need to combine the Data of both recordsets. I need EVERYTHING in Table B, but I also need All Table A records that match the phase selection.... Can I write this one query or do I need to use a Temp table?
View Replies !
Delete From Table Statement Doesn't Delete
I have a weird thing happened in my application where I have run a statemement Delete from tblmysqlReceipt; but later I checked through in my log file there line which have been delete is still exist. If any one can help me I can even post the log files too.
View Replies !
Need To Delete Duplicate Records.
I used LOAD DATA FILE to load data into a mysql database. I discovered that there are lots of duplicate records. Can anyone give me a simple list of mysql commands to search and destroy duplicate records?
View Replies !
Delete Duplicate Records.
I used LOAD DATA FILE to load data into a mysql database. I discovered that there are lots of duplicate records. Can anyone give me a simple list of mysql commands to search and destroy duplicate records
View Replies !
Delete Related Records
I have a typical setup with a master table that has a related table with many records per master record. Since MySql does not support sub-selects, how do I delete just the set of records I want? TABLE1 - Master id, name TABLE2 - Tran id,mast_id,item,price Tables are related via (TABLE1.id = TABLE2.mast_id) I wish I could do a: DELETE FROM table2 where mast_id IN (SELECT id FROM table1 WHERE name = 'fred'); If I'm only deleting one master record, then it is easy, one select to find the master.id then another to delete all table2 records with that mast_id. But, the problem I'm having is when I want to delete a batch of records from table1, I also want to get rid of all of the related records so I do not have any orphans.
View Replies !
Delete Certain Word In All Records ?
I have a table in my database called jos_content (joomla website), which contains all news articles from my site. Each newsarticle has a exclamation-mark in the title (its allready in the rssfeed). I want to delete this exclamation-mark from each newsarticle automatically. Is there any SQL command to do this ? If so, can i put the command in a php file and run a cron job every 10 minutes or so?
View Replies !
How To Do Bulk Delete Of Records?
I have MySQL with Phpmyadmin front-end on a Centos machine. I have a table with with two fields - Member_id & member_name. Both fields are unique. Now, the table has over 100,000 records and I have to manually remove about 5000 members from one table and add them to another similar table. I have the list of members to be deleted - member_name in a text file, But Phpmyadmin allows only searching one-by-one. How do I do a bulk delete of all these 5000 members?
View Replies !
Delete The Records That Occur More Than Once
I have two tables...(the tables contain some duplicate entries) I need to combine them and then delete the records that occur more than once....this means not leaving any entry for the duplicate... Is there any simple way to do this?
View Replies !
Find & Delete Orphan Records
Have 2 tables ts_sheets : TimeSheet_ID TimeSheet_Date Emp_ID Orphan LineItem TimeSheet_ID Client JobNumber etc The following sql will show me all the ts_sheets records with no ts_lineitem records select s.* , l.timesheet_id from ts_sheets s left join ts_lineitem l on s.timesheet_id = l.timesheet_id where l.timesheet_id is null I would like to delete these orphan records Something like UPDATE `ts_sheets` SET `Orhan` = 'Yes' left join ts_lineitem l on s.timesheet_id = l.timesheet_id where l.timesheet_id is null Than delete `Orhan` = 'Yes' I can not work the syntax out
View Replies !
Unable To Add/edit/delete Records
I have a mysql db - 4 tables - all basicly the same. Three tables permit me to add, edit and delete records from Access via ODBC and one will not. - I am no expert at all this and need some pointers as to where to start looking for the problem. The tables all worked fine before they were recently moved to a new server/host. I have asked this question before and had no replies at all. I really would at least like someone to point me towards somewhere I could get help even if this is not the right place.
View Replies !
Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is: The key of one of the record has the format: Rssss The key of the second record has the format: ssss How can I delete both records from the table?
View Replies !
Database Size Not Decreasing After Delete Of Records
I have tried deleting hundreds of records from several tables in my MySQL database and I have found that although the number of records in these tables has decreased, the size in Mb has remained the same. I checked the data directory and the .MYI files seem to have been amended but the .MYD files have remained the same size. Is the data still there? I really want to remove it both for security and to keep file sizes down.
View Replies !
Lock Tables To Update And Delete, But Insert New Records
I'm trying to "lock" mysql tables - to prevent rows being updated/deleted - I still want to be able to insert new rows (legal reasons). I have toyed with the idea of changing the user permissions - can I simply change it so that no-one can perform an update on that table, even root? But I feel there must be a simpler way that I and my many web searches am missing.
View Replies !
Stored Routine To Delete Or Not Show Outdated Records
DELIMITER $$ DROP PROCEDURE IF EXISTS `DB_NAME`.`PROCEDURE_NAME`$$ CREATE PROCEDURE `DB_NAME`.`PROCEDURE_NAME`() BEGIN DECLARE TodaysDate DATE; DECLARE ListingEndMonth DATE_FORMAT(DATE(%M)); DECLARE ListingEndDay DATE_FORMAT(DATE(%e)); DECLARE ListingEndDate DATE_FORMAT(DATE(%M %e)); SET TodaysDate = DATE_FORMAT(CURDATE(%M %e)); SELECT Listing_ID, month_end, day_end, do_not_show FROM My_Listings WHERE do_not_show != 1 AND sale_length != one_day; SET ListingEndMonth = STR_TO_DATE(month_end); SET ListingEndDay = STR_TO_DATE(day_end); SET ListingEndDate = STR_TO_DATE(month_end day_end); foreach(Listing_ID) { if ListingEndDate > TodaysDate { UPDATE My_Listings.Listing_ID SET do_not_show=1; } } END$$ DELIMITER ;
View Replies !
1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network. I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables. I've tried following query DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11; But it gives ' error in query.
View Replies !
How To Cascade Delete Related Records Without Deleting The Main Record?
Let's say I have the following database tables: CREATE TABLE user ( id INT UNSIGNED NOT NULL PRIMARY KEY, username VARCHAR(20) NOT NULL, ) ENGINE = INNODB; CREATE TABLE user_email ( user_id INT UNSIGNED NOT NULL PRIMARY KEY, email VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB; CREATE TABLE user_phone ( user_id INT UNSIGNED NOT NULL PRIMARY KEY, phone VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB; What I want to do is delete all records in the 'user_email' and 'user_phone' tables without deleting the record in the user table. I have a database without nearly 20 tables with one-to-one and one-to-many relationships with the user record and I need a way to cascade delete records in relationship tables.
View Replies !
Selecting Records Belonging To Multiple Records Through Join Table
I have is 3 tables, 'categories', 'levels' and 'categories_levels', the 'categories_levels' table is a join table, with the `id`, `category_id` and `level_id` fields. Categories can belong to many different levels, so records in the join table might look like this (assuming that the category has an ID of 5) id, category_id, level_id 1, 5, 1 2, 5, 2 3, 5, 3 So you can see that category 5 belongs to 3 different levels (1, 2, and 3). On a web page, I've got a multiple select box, which allows a user to filter the categories depending on which levels they belong to. What I need a query to do is use that join table to find all categories which belong to the selected levels. Now for the tricky bit. Only the categories which belong to every single selected level should be displayed. So in the example above, if I select level 1, then category 5 will be displayed, but if I select levels 1 and 4, then category 5 should not be displayed, because although it does belong to level 1, it doesn't belong to level 4 as well.
View Replies !
Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured. A & B Table A, has column: Product (product code) as primary key Table B doesn't. Apart from that they have the exact same fields. There's also a column: supplier I want to Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes. mysql version 4.0.27
View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
How To Delete Rows In A Table Where Url Shows Only 1 Time In Whole Table
I don't know a whole lot about mysql, i need to fix some things in my db and what i described in the title is the first thing id like to do. Now, I have a table which lists referring urls, clicks to them and the time. Everytime i get a hit from lets say url1.com it gets a new row with a time id (like 1218613809). I am not resetting this table since it has to track the incoming hits forever since the moment they start sending traffic. But now the table starts getting the hickups now and then, it has about 850.000 records in it and it cant handle it anymore. I need to do a 'repair table' action and then it's good again, but it happens more and more often lately. So first i want to delete the rows of the urls who have only sent me 1 click. These urls who sent me 1 click only have one row. When an url sent me 20 clicks, that url is in 20 rows in the table. So I want to delete the rows of the table with the url's that only show once in the entire table. How do i do this?
View Replies !
INSERT INTO SELECT Taking Forever But SELECT Part Is Fast?
I have the following mysql code to insert about a million rows. This takes quite a long time (over 50 min). However, when I just run the query alone without the INSERT command, the query itself only takes under 5 minutes. Is there a way I could speed up the inserting?? insert into tblc select tbla.* from tblb inner join tbla on tbla.issuer_id = tblb.issuer_id;
View Replies !
SELECT Takes 20 Seconds
I am having trouble speeding up a SELECT statement from a table with 1,764 records. I tried myisamchk --sort-index --sort-records=1 but this did not help (is a Primary key enough?). Things were fine at around 1200 records but went downhill after 1700. I am using MySQL 3.23.42 on HP/UX 11.11 and PHP 4.3.4. Any tips on how to speed things up?
View Replies !
Update Takes Too Long, I Need Help
here is my update SQL string: update prices, legend set prices.legend_id=legend.id where ( (prices.id between 1 and 10006) ) and prices.a_legend=legend.description running time: 63.97960 as you see prices has 10006 records and legend table arround 349 records please advice how to speed up this?
View Replies !
How To Delete All Rows From One Table With Id NOT Found In Second Table?
How can I delete all rows from table `one` with id NOT found in table `two`? Here is my situation: Table `one` contains transaction on items identified by `id` Table `two contains detailed information on items, identified by the same `id`. Periodically, items are be deleted form Table `two`. My question is: How do I find (and delete) all the rows in table `one` that refer to an `id` that is NO LONGER in table `two` ?
View Replies !
Create Index Takes More Than 5 Hours
I'm running MYSQL on a windows xp laptop with an intel centrino 1.60 GHz Processor and 512MB RAM. I've created a table and imported 27 million rows of data. I wanted to create an index on one char(55) field, as this field is a key against which many selects will be made. The information in the field is not conducive to having an integer key as any queries would require an extra join to the look-up (reference) table. Creating the index ran for more than 5 hours and finally gave up when there was no more space on the drive (creating the index apparently consumed more space (11GB) than the actual table (8GB). I'm wondering if this is normal time and space consumption for 27 million rows. Finally, doing an un-indexed query on this table : SELECT * from historical where reference = 'abcd'; Takes more than 1/2 hour. Is this also a normal amount of time? I understand that people are using MYSQL for data warehousing and major transactional applications.
View Replies !
Query Takes A Long Time
On my site I have a query that searches through 1,7 million. The php-file with the query takes a bit of time to load. Is it possible to show some sort of progress-bar during this time?
View Replies !
Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to display the results based on the user input. The results might be anywhere from 100 to 1000 per query and presently I am displaying them as 25 results per page. Problem: Each query is taking about 20-30 seconds. My solution: I have tried to optimize the table and also index the table. I have actually converted a MS access database to SQL database, so it wasn't previously indexed. Both optimization and indexing doesn't give any good results. I always get a timeout. ie. it takes longer after indexing and optimizing. 1. I was wondering if someone has a creative solution for this. ie. reduce the time from 20-30 seconds to atleast 10 seconds. 2. I have links of pages of results beneath the first page result. When each of these links are clicked it takes 20-30 seconds again. Is there a way I can reduce the time taken for the subsequent pages are reduced? I cannot use the LIMIT option in mysql, since I have a where clause which has to search through the whole table. I tried using views and using limits, but it takes as much time.
View Replies !
JDBC Result Closing Takes Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
Easy SELECT With OR Takes A Very Long Time
We've got a database with about 1000000 books. A query in the table BOOKS for the TITLE 'java' is very fast. We have a fulltext index on the column TITLE. However, if we want to do a exact same query and include the rule that the book with ISBN '0131016210' always should be included - then the query take several seconds to finish: SELECT * FROM C_BOOK WHERE MATCH(NAME) AGAINST ('java') AND (ISBN LIKE '0%' OR ISBN LIKE '1%') OR ISBN = '0131016210' ISBN is the PRIMARY KEY. Is there anything I can do about this?
View Replies !
Backup With Mysqldump Takes Hours, Database Is Not That Big
Hello, I've been having some problems making database backups. I've searched all around and I notice that to most people it only takes a few minutes to backup huge databases (1+ gb). My database is around 350mb and it can take up to 4-5 hours to backup, which I guess it not normal at all. My server has a dual Xenon with 4gb ram, using mysql 5.0.27-1.fc6 and php 5.1.6-3.6.fc6. I am using Fedora Core6 with Plesk. The database is around 350mb, with around 1.1 million rows. To backup I use mysqldump -uroot -p database > backup.sql As I said, the above takes hours. I guess it should take minutes?
View Replies !
JDBC Result Closing Takes Ists Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
Delete From Two Table
I have two separate tables - ADDRESS and CUSTOMER I want to delete all the ROWS under the table ADDRESS, WHERE CUSTOMER CUSTOMER_ID=1 (CUSTOMER_ID is located in the CUSTOMERS table.)
View Replies !
Delete A Table
i have noticed that there are loads of unwanted table in my mysql database and i need to completely delete those tables..but i find only two options through myphp admin...empty and drop.. but i need to delete the whole table ..how can i do it??
View Replies !
Inserting Data Into Existing Records Of A Table From Other Table
how to insert data from one table to an existing records of another table.... In specific how can i insert data into a table....in such a way that this data should not be inserted as a new record,rather it should be inserted into the existing records(these records at first contains only two fields of data..the remaining fields contain default values) my question is that how to insert the data into these remaining fields from another table.(more specifically what is the insert statement used).
View Replies !
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
View Replies !
|
TRACKER
