Very Slow Table Update
I have two tables. One is really a subset of the other. However, they came in different data files and I would like to pull data from one and put it into the other. However, it is VERY slow!
Once the tables are setup I will only read from them and perform operations. I will never update or insert. However, I can't get things setup to that point. Code:
View Complete Forum Thread with Replies
Related Forum Messages:
Update Too Slow
I need to update 25 * 5000 records, if I do one at the time it takes too long time, do any one have a good proposal ?
View Replies !
Slow Update
The following query can sometimes take up to 2.5 seconds to execute on a table with only 150,000 records. UPDATE items SET item_views = item_views + 1 WHERE id = 5897; is there any way I could speed this up? Some setting I could change to make MySQL faster for this? The field "id" is the primary table key.
View Replies !
Very Slow Update Statement
I am having an issue with an UPDATE statement that takes a very long time. I am using 1 table in a schema to update another table in another schema. Below are the create statements and the update statment I am using. Table and column names have been changed to protect the innocent :) Code:
View Replies !
UPDATE Function Very Slow??
I have a large table (77,321 Rows) and I'm trying to update it. For some reason, UPDATE takes a long time. Maybe it's my query? I'm doing it through php, maybe that's it? I'm running the program locally using the CLI, and it still takes ages. Should I not expect it to be as fast as SELECT functions? $update ="UPDATE `ch_products` SET `products_weight` = '".$IDS[$i][Weight]."' WHERE `xref1` = '".$IDS[$i][ID]."'"; How can I make this faster?
View Replies !
Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows. My table is getting kinda slow here is the query i am using UPDATE nametable SET sectionname = replace(sectionname,'".$oldsec."','".$sec."'), categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=Ƈ' where sectionname='".$oldsec."' and categoryname='".$oldcat."' ; I am wondering if the same thing is possible with an insert... on duplicate key statement? I cant seem to get the insert statement to work, but not even sure whether it is appropriate. With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)
View Replies !
UPDATE Large Number Of Rows With CASE Is Very Slow
UPDATE table SET rating = (CASE uniq WHEN 'ACD' THEN 0.6857 WHEN 'GGYB' THEN 0.5831 WHEN 'QUCG' THEN 0.5831 WHEN 'CYP' THEN 0.5831 ... ELSE 0 END), score = (SELECT @t := 1/(1+rating))*votes_up - (1-@t)*votes_down; The table has the column `uniq` as PRIMARY KEY of type VARCHAR(10), and about 500 rows. I need to update the entire table with this query. The problem is the query runs extremely slowly, taking 4.6529 seconds to run. I realize the index is not the most efficient, but is it the main culprit?
View Replies !
Table Gets Slow At Around 60k Records
I've a table that starts getting slow with count(val) and select ... order by when it has around 60k records. Table type is innodb Strange enough, at 50k records the response time is great, but looks like it grows exponentially after that. Its something like 50k records - 0.2 seconds for count(val) 60k records - 3 seconds 100k records - 20 seconds I realize order by and count forces mysql to go through the entire table...
View Replies !
815,000+ Records, DB Table Query Too Slow
I have a database table with about 815,000 records in it, each record containing the source of individual webpages (with all the tags and multiple spacing stripped out leaving just the words). At first my match boolean queries were fine but once I got to around 100,000+ records it became too slow to use for a web-based search tool. I was able to separate the data into 10 tables and then use AJAX to run the match query on all 10 tables at the same time and that has increased performance. My question is, is it possible for me to increase the performance for the 1 table some how instead of resorting to separating the data into multiple tables? Is MySQL the right DB for this kind of setup, would MSSQL or PostgreSQL perform better for larger sets of data? I'm not sure how MySQL 5 clustering works,
View Replies !
Slow Table Access On Local Machine
I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it through ODBC (MyODBC-3.51.10-x86-win-32bit). I'm developing a .NET web application, but I don't think that's relevant. I already had the application running OK on my desktop, and when I created the same environment on the laptop, retrieving trivial amounts of data (5 rows) from a table is taking 6 seconds. Updates the same. IN otherwords any table access takes 5 or six seconds. (Table only has about twenty rows in it). I can connect to the database on the laptop from the desktop (i.e - application runs on desktop, retrieves data from laptop - works fine, not noticeably slower than local on desktop. If I connect the other way - application on laptop, database on desktop, it runs just as slowly as locally. The effect is the same whether I connect to 127.0.0.1 or via the machine name. Code:
View Replies !
Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do: There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns. The most common query is SELECT data FROM table WHERE n1 = ? The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column. Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset: SELECT COUNT(1) AS total FROM table WHERE n1 = 3; index = random number from 1 to total SELECT data FROM table WHERE n1 = 3 LIMIT index,1; How can I speed this up? I need it to take less than half a second if possible. Thank you.
View Replies !
Slow SELECT Query INNODB Table
I have a couple hundred connections doing "SELECT [Char36 Field], [LongLong Field], [Long Field] FROM [Connection Specific Table] WHERE [NonIndexed VarChar36 Field]=[Value]". Notice that the table the select statement is being called on is unique to each connection. These tables have less than 10000 records, but this statement can sometimes take over an hour to execute. It is an INNODB table.
View Replies !
Slow FullText Search When Table Grows Constantly
I have a small issue with a web-search engine I'm working on. The main table is constantly growing (1 insert per second, currently 150 000 records) and it has full-text indexes on 2 fields that contain over 20 000 characters on each row. The thing is, as I could observe, that when multiple different full-text searches are made in appropiate period of time, the query doesn't take so much to respond (though i couldn't quite say it's fast), but when no search is made, let's say, for over 2-3 hours, the same query takes up to 30-40 seconds to respond. The query is simple: .....
View Replies !
Multiple Table Calls And Slow Loading Times... Joins?
just recently started using MySql and I think I've got most of the basics down - everything WORKS just not well. Essentially, I'm making an image gallery and the search/landing pages have thumbnails, pretty straightforward. The thumbnails are rollover slideshows of the images in the gallery so for each thumbnail preview there are a varying amount of actual thumbnail images that are loaded. The table structure is to this effect: gallery table: gallery_id, gallery_views, gallery_rating etc etc thumbnails table: gallery_id, thumbnail_link So i may have an entry as such: gallery_id = 21, gallery_views = 300, gallery_rating = 60 With several thumbnail entries: gallery_id = 21, thumbnail_link = url/image1.png gallery_id = 21, thumbnail_link = url/image2.png gallery_id = 21, thumbnail_link = url/image3.png What I have been doing thus far, say for the index page I grab the top 30 by views, is something like "SELECT * FROM galleries ORDER BY views DESC LIMIT 30" and then after bringing that into PHP in my while statement on each iteration I make a separate call that is like "SELECT thumbnail_link FROM thumbnails WHERE gallery_id = '$gallery_id'" and then output all the thumbnails for the rollovers. So let's say then for example that I want to show the top 30 by views, as well as the top 30 by user rating. At that point I'm making two calls to the galleries table, which arbitrarily we'll say contains 50,000 entries, but I'm also making 60 individual calls to the thumbnails table, which contains in some cases 20 or 30 thumbnail links per gallery and contains upwards of a million rows. Obviously my loading times are much higher than I would like them to be and I can't imagine this is the optimal way of making these calls. It seems like a fairly elementary concept but I can't seem to find something that works.
View Replies !
Why LAN Clients Frozes While A Slow Networked Client Downloads A Big Table
With MySQL 4.1 I think from a day to another something changed in the working of our server. When a remote agent with very slow Internet connection downloads 60.000 rows query lets say downloads the whole "products" table, all of the other fast LAN clients stop responding until the big download finishes. It is possible, that they would like to change the "products" table while the remote client downloads it... But! There is no lock to the table, just its a simple "SELECT a,b,c,d FROM table ORDER BY a" query.. Can it happen because of that the table becomes bigger and bigger, and now MySQL can't copy it to in the RAM, and let others live, and now it stops other clients instead?
View Replies !
Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day. This is my query: select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable group by mydate It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....
View Replies !
INSERT Into New Table, SELECT From Old Table, UPDATE Old Table With New Key
Code: INSERT INTO retailers (retailername, retailerdesc, retailerwebsite, retailerurl, active) SELECT datasource_retailername, datasource_retailerdesc, datasource_retailerwebsite, datasource_retailertrackurl, 1 FROM datasources_retailers_idx AS i LEFT OUTER JOIN retailers AS r ON r.retailername NOT REGEXP REPLACE(i.datasource_retailername, ' ','.+') UPDATE datasources_retailers_idx SET idretailers=last_insert_id(); Here's what I'm trying to do: * Select from old table * check if there is a matching retailer in the new table * If not, insert retailers details into new table * Update a reference column in the old table with the INSERTID primary key value of that row from the new table
View Replies !
Slow Queries When Using Views Compare To Direct Table Queries
I’ve having problems with my server load for a while now. I have two tables with different content, but I need to display them in the same results, so I created a view with a union all (named: top_news_videos). The problem that I’m seeing is that when running a select to the view it takes a lot longer (and in that way, more server intensive) than running the query directly to one of the tables. For example, I created a page where I run 7 queries similar to this one: MySQL Code: ...
View Replies !
Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows: Customer table contains: 1) customer_email_address 2) customer_newsletter (value 0 or 1) Visitor table contains: 1) email The visitor table contains email addresses from customers that have signed up through another system. I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.
View Replies !
Update Table With Info In Other Table
i have a case like this: table table1 (key, accumulator) table table2 (key, counter) i want to, for each table1.key = table2.key, update accumulator with the info in counter, something like this: update table1 set accumulator=accumulator+table2.counter where (? counter is the value related with the same key as the one in table1) how do you write a sentence like this one?
View Replies !
Update A Table With Data From Another Table
I have two tables with similar data. The firs table contains data that is to be updated with data from the second table. The first table (tblA) has a unique key, but the second table (tblB) does not. I have to use the 'lastname', 'firstname' and 'dept' fields that are in both tables and join the tables on those three fields. I have tried: update tblA, tblB set tblA.empPty=tblB.empPty where ((tblA.empLName=tblB.empLName) and (tblA.empFName=tblB.empFName) and (tblA.empDept=tblB.empDept)); with some test data where I know I have a match using the three fields, but nothing gets updated.
View Replies !
Update Table With API
I'm trying to develope a getway between matlab and mysql. I would like to write the result of matlab routine into mysql table without "UPDATE .....SET..." statement, because I've have to write a different value for each row and I have to write a lot of row. I would like to write table row by row sequentially.
View Replies !
2-table UPDATE
I am running the following query through PHP's mysql_query: UPDATE hotel, hotel_brand SET hotel.hotel_brand_id=0, hotel_brand.hotel_brand_parent_id=0 WHERE hotel.hotel_brand_id=6 AND hotel_brand.hotel_brand_parent_id=6 In actuality, it's two queries combined into 1. I am deleting a value that rows in these 2 tables reference, and want to set the values to 0. Of course, the problem is the values aren't changing to 0. Is splitting these up the best solution?
View Replies !
Update Table From TXT/CSV
I have a table With Field1(INT,8,PK), Field2(INT,6,PK), Field3(VARCHAR,255) and Field4(VARCHAR,255). I have to update records from a FIXED LENGTH TXT that contains: Field1(8 digits)Field2(8 digits)Field4(1-255 digits) or the same in CSV....
View Replies !
UPDATE Table SET
Does anyone know how to implement this into a php form CORRECTLY? I have all the proper syntax but then the server gives me a message that says the mysql version may not go along with the syntax. How do I work around this then? I need to update query strings and such and nothing will work.
View Replies !
UPDATE From One Table To Another
This should be so simple but I'm getting a very strange error?? I'm using MySQL version: 4.1.12-log I've got an outdated country table that I'd like to update info from an ISO table. Pretty straight forward if you ask me. Here's the query: UPDATE country, isocountry SET country.un_numcode=isocountry.numcode WHERE country.country_code=isocountry.iso; I created a column called un_numcode and then I'd like to put the iso numcode info in my table where the country_code = iso code (same data, different field names in different tables). It goes along really well for 38 rows out of 239 rows?? And then I start getting 127 in ALL the un_numcode fields even though that value does NOT exist in the country.numcode table!
View Replies !
Update One Table
I have 2 tables one for the team standings one for the scores, this select statement will calculate win loss : SELECT SUM((homescore>awayscore and home=teamid) OR (awayscore>homescore and away=teamid) ) as wins, SUM((homescore>awayscore and away=teamid) OR (awayscore>homescore and home=teamid) ) as losses FROM scores, teams order by teamID
View Replies !
Table Update
How do I relate such a way that I update a column of the main table, another secondary table's column related to the main table gets updated automatically?
View Replies !
UPDATE From A Linked Table
I wanna update the table COMPANY from a linked field. The following is not accepted. Could anybody help me with what the command should be? Thanks a lot. UPDATE `COMPANY` SET `COMPANY`.Region_ID = ( SELECT `Area`.Region_ID FROM `AREA` WHERE `COMPANY`.Area_ID = `Area`.Area_ID )
View Replies !
Update A Table With Values In Another
I have the following table tableA column_a column_x column_y column_z tableB column_x column_y column_z How do I update the rows of tableA that match rows of tableB (all rows of tableB are unique) What I want to acheive is a check against tableA.column_x with tableB.column_x, if they match, then update the row in tableA.column_y with the value of tableB.column_y and tableA.column_z with tableB.column_z
View Replies !
Update Table By ID Ranges
How do i use the UPDATE statement to update a range of Primary Key id numbers. (let say 100 through 1000). Example, UPDATE testTable set testField='9999' WHERE test_id in (100:1000);
View Replies !
UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.
View Replies !
Automatic Table Update
i have an database in which i need to update a status filed of an table with the date. i,e those datas which gets expired are to be updated.. so need to run a command in mysql by default.,
View Replies !
Multiple-table UPDATE.
I'm trying to update multiple-tables at the same time but I read somewhere that you could do it if you had mysql version 4.0.4. But currently, I have version 4.0.0 and I want to know how to do it with this version
View Replies !
Update Table Problems
I'm trying to update a table where specific values are in another table. Here is the update statement I am using: UPDATE Route_Detail,Routes SET Route_Detail.Trip_Calc='S' WHERE Routes.Route_ID = Route_Detail.Route_ID AND Routes.Project_Code = 'OSUT' AND DATE_FORMAT( Route_Detail.R_Date, '%m%Y' ) = 072006 This statement runs but does not update any rows. This select statement returns 11 rows: SELECT RDetail_ID FROM Route_Detail INNER JOIN Routes ON Routes.Route_ID = Route_Detail.Route_ID WHERE Routes.Project_Code = 'OSUT' AND DATE_FORMAT( Route_Detail.R_Date, '%m%Y' ) = 072006 How do I get the UPDATE statement to update those 11 rows. I'm using MySQL 4.1.21-standard version.
View Replies !
Fields In Table Will Not Always Update,
I'm using C#, ASP.NET and MySQL, The language is no problem (not to much) but the rest well I wonder. I have a DB table that has 5 seperate fields. A main one that is Integer and the rest are VarChar(50). each of the 4 others are identical in setup. I can change some records and others I can't. ID int(10), std varchr(50), atd varchr(50), dtd varchr(50), ctd varchr(50) I update say 'atd' with new data, Date, Time and a code of 15 letters/numbers.This one takes, I change to another record and try to update ctd and it shows like it takes but when I use the Command Line it shows no updates on ctd. this is random on this also. If I change to another record it may all work or not. Any Ideas at all?
View Replies !
Update Table With Java
im trying to update my table bunt int test=5; String update="Update bunt SET buntnr='test' Where bunt_ID ='ver.bunt_ID'"; wich results in this error Data truncation: Out of range value adjusted for column 'buntnr' at row 1 If i use this, changing variables to Ãnt it works fine! String update="Update bunt SET buntnr=5 Where bunt_ID =1";
View Replies !
Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement: UPDATE ApplicationTbl INNER JOIN AcademicTbl ON ApplicationTbl.CampusID = AcademicTbl.CampusID AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period SET ApplicationTbl.App_Status = 'Qualified' WHERE AcademicTbl.Sem_OnCampus >= '1' AND AcademicTbl.GPA >= '2.4' AND AcademicTbl.Judicial_Sanction IS NULL It keeps saying its wrong. Even though I know its not
View Replies !
Update Records In Table
i have installed a mysql server, and i have a problem with import record in database. the origine is a file txt or csv, the destination database contain also other records, and i must update the records into database
View Replies !
Update Table Values
I have a csv.txt file with contacts and their information in it. Then I also have a table in mysql with the same contacts and their various information. I need to merge these to so that if a field value in the database is NULL the value that is in the csv.txt file is inserted into the database field. I have been looking at the mySQL site on how to do this but have not been able to produce a working solution. Below is a more detailed example of what I am looking for. Code:
View Replies !
Update Table From File
I've a file with 6000000 UPDATE commands. I'm planning to execute those commands on BANKING database using: mysql banking < update_table.txt Is this the best way to execute many updates on the database, without affecting performance? I'm worried to overload the BANKING database with those long updates... What do you think about it?
View Replies !
UPDATE A Table, Using GROUP BY
I have a table with the following fields -- schoolcode (e.g., 313) teachername (e.g. JONES) studentname (e.g., JOHNNY DOE) totaltestitems (e.g., 50) totalitemscorrect (e.g. 40) studentpctcorrect (e.g., 80.0) teacherpctcorrect (????) (NOTE: schoolcode + teachername combination would be unique.) I want to UPDATE the table with the average percent of items correct for students assigned to each teacher (teacherpctcorrect).
View Replies !
Update Count From Another Table
I am trying to write a query that takes the count() from one table and updates a second table with the value. This is what I have so far: UPDATE wp_posts SET wp_posts.comment_count = SELECT count(comment_ID) as comment_count FROM wp_comments where wp_comments.post_ID = wp_posts.ID What am I doing wrong?
View Replies !
Daily Update Of A Table
I've a MySQl db. I've a table "example", with 3 columns: code::int date:date status:bool (0 or 1) I need each day something checks my table and calculates difference between current date and date in the second column of my table. ....
View Replies !
Update, Table Locking
Is it possible to have update in a iterative loop with start index and range wtih the query? I wouuld like to have the update happen in steps with loop to avoid getting the entire table locked. Also, I do fine the query takes around 10.88 secs to update to complete. this is one reason i prefer to have update happen in steps so that entire table locking do not happen. its innodb table.
View Replies !
Update Values In Another Table
Table one has columns thread_forum_id and thread_thread (and a bunch of other stuff) Table two has columns topic_id and post I would like to populate thread_thread with the data from post however the columns thread_forum_id and topic_id need to correspond to each other when i do this. for instance if topic_id = 1 and post = A and another row is 2 and B then i want the resulting table one to have 1 and A 2 and B but not 1 and B or the other way around.
View Replies !
Multiple Update Same Table
I have a table that i need to have only 1 field in the same column be yes and the rest turned to no... I have tried SELECT * FROM tab1; UPDATE tab1 SET 'field' = YES WHERE id = 5; UPDATE tab1 set 'field' = NO WHERE id != 5; Obviously running two at the same time does not work but if i can somehow combine these two to do it all at once...
View Replies !
Update A Big Online DB Table
I have a big MySQL table with about 3000000 records (size 800MB), this table is updated twice a week with about 120000 new coming data and some old records need to be removed. Ideally, I handle the update (with some bash/Perl scripts) on my development server where I do lots of post-processing[i.e. field validations] which can not simply done by MySQL update statement. And then I upload the new table to the production DB servers, and use the following command-line to update the table: mysql -umyname -pmypassword dbname < mytable_dumped_from_development_server.20090311.sql This works well except it takes a long time (about 10 minutes) to finish, and during the updating, the database is locked and the website is therefore frozen. I know I can write a script [use mysql REPLACE INTO, instead of the above command line] to directly update the table on the production server, at least no long time table LOCK although it's more risky. Is there a best practice or common method to update a large live MySQL table[usually twice a week] and meanwhile take less impact on the website. my system is: MySQL 5.0.22 and RHEL-5.
View Replies !
|
TRACKER
