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 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 !
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 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 !
How To Code Sql Update Using Aggregate Function
These 2 alternative queries express what I want clearly: update phpList_work p, order_info o set p.oid=Max(o.oid) where p.cid=o.cid UPDATE phpList_work p LEFT JOIN order_info o USING (cid) SET p.oid=max(o.oid) they get the error #1111 - Invalid use of group function phpList_work is a list of Customer records. phpList_work already has a subset of the customer list, with ID values (cid) order_info is the order master, keyed by its id (oid) and has cid The invalid query shows I want to select the order_info value of oid that is the MAX(oid) for each value of cid in phpList_work. The manual does not discuss this. If the functions do not support this obvious and useful syntax, how do you do it?
View Replies !
IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere! The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....
View Replies !
Slow
What generally would be the reason why all my db driven sites are running slowly or even hanging. I am on braodband speed but just changed hosts.
View Replies !
Very Slow Select
The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong? I'm also including the dump of the table definitions. This is a cd cataloging database. Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames' table yet. Code:
View Replies !
MySQLdb Slow
I have a Python program that parses a file and inserts records into a database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my parser runs *really* slow. Writing out to CSV files is fine, but when I try to insert the same records in a MySQL5 database, it slows to a crawl. Using MySQL 4.1 seems fine. The data seems to be inserted correctly, it's just really slow.
View Replies !
Slow Restore
Mysql 4.1.15 on Win2k. Using InnoDB. Using the mysql administrator gui to create a backup, everything goes fine, and restores quickly. Using the command line: mysqldump %dbname% --single-transaction > %dbname%.sql creates a file about 15% smaller than the gui produces, and is EXTREMELY slow to restore. I have tried adding locks, skip opt, everything. What does the gui use for a command to create this dump?
View Replies !
HEAP So Slow
I have a heap database, with 1.5-1.6 milion rows. on that is 2 columns... ID | Title title is indexed. When i run a query like this ------------------ SELECT index_data.* FROM index_data INNER JOIN `index` ON index_data.id=index.id AND index.title LIKE '%$query%' WHERE playtime > $dur... The execution time is about seconds... ------------------ Even a single like statement just on `index` (heap) takes 3-6 seconds. Here's the table stats... ---------------------------- Data 397,442 KB Index 24,639 KB Total 422,081 KB ---------------------------- Why??
View Replies !
Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.
View Replies !
Slow MySQL
mySQL has been running very slowly and I am getting errors. First I did 2 things I raised the ServerLimit number (apache) to allow for more connections, I also raised the max conncetions in my.cnf. I do not know if this took effect? That should have worked. But basically in phpmyadmin i get this error frequently. I am getting more traffic so I think it is that. MySQL said: Documentation #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
View Replies !
Slow Db Access
I have worked with a few mysql dbs on different servers but i have recently been asked to work with one on nicnames. It seems horribly slow. Working in phpMyAdmin (which i had to install myself) it takes ages when i want to do anything. View the table structure, view the data..etc. Any way i can test the speed so that i can compare it against another server i work with and proove there is a speed issue and take it to nicnames cus it is crazy and is going to affect the speed of the website!!
View Replies !
Slow Insertions
I have a MyISAM table with fixed width rows (ie no vars). I also have keys disabled. I am running a huge data load process. When it first started it was inserting about 38 records a second. Now, after about 200,000 records, it speed is about 22 recoerds per second. Whats going on here? and how can I improve it? I am loading about 15 million records, so the difference between 22 per sec and 38 per sec is SIGNIFICANT (ie 2 days).
View Replies !
Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table. Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.
View Replies !
Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form. My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot? Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?
View Replies !
Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished) select date from temps where date in (select distinct date from observations where camera like "a") The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?
View Replies !
Slow Connection
I build an application and installed it on many machines. In every machine except two, the program works without problems. On this two, the connection with database is too slow. I saw the opened doors with 'netstat' and the computer opens about 5 or 6 ports (to the port 3306 of the mysql server) before sucessfuly connect with MySql Database and execute the sql. I don't know what could be happening. I realy need to fix this because the progrm is too slow with this error. Could anyone know what could be happening??
View Replies !
Slow Query Log
my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?
View Replies !
Slow Query Using NOT IN
I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....
View Replies !
Slow Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor) This is the query : SELECT NPL, PP FROM P_A INNER JOIN ACTIONS ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in (SELECT NA FROM P_A WHERE NP ='ABC') P_A has 5 columns and 12000 rows Actions has 5 columns and 770 rows Any suggestions ?
View Replies !
Slow Request
I want to run the following request: mysql> select count(*) from fingerprints where fingerprint in (select fingerprint from fingerprints where id_file=3263); where fingerprints is the following table: ....
View Replies !
Join Too Slow
I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?
View Replies !
Why Does MySQL So Slow
I just changed to use MySQL few days ago but it was a bad idea. My server now is running very slowly with the database. I'm using Perl5 and DBD::Mysql in my script. The system is Linux9, Apache2. I looked at these mysql pid and saw a lot of activities (about 400) while there are more 100 users online at this moment and lots of running under a the same pid number.
View Replies !
Slow Max() Selection
I have a large table with some fields. Two of them are: year char(1) && year's rightmost character OrderNo integer Both are keys (BTREE). I need to select the last order in the year. select max(OrderNo) from ORDENES where year='8' While the year is growing the selection speed id decrement (12 seg).
View Replies !
Slow Connect
Does anybody know why it sometimes takes more than 10 seconds to connect to a database and sometimes it just takes half a millisecond?
View Replies !
Slow Connections
I am using MyODBC-3.51.11-2-win on Win 2003 OS. I am not able to see all of the connections in the list under the System DSN tab. The connections that show allow the ASP pages to run at an expected rate. However, the ones not showing in the list are running extremely slow. If I attpemt to recreate the connection I am told that the connection already exists and asks if I want to replace the existing connection. Wheter I click yes or no the connections do not show and the pages run slowly. How do I get them to show or resolve the issue. The ASP code is the exact same SQL statements and connection strings as the in previous applications.
View Replies !
MySQL Slow
I had downloaded a few years back mySQL v3.51 installed but never used it. Now I wanted to convert some B-TREE databases to mySQL and did some testing via ODBC to insert 70,000 records: My results: MS ACCESS: ~60,000 msecs MYSQL v3.51 ~18,000 msecs Impressed with the speed, I went ahead and got the latest MySQL v5.1. Uninstalled the older version, I had nothing there to preserve, so I did a simple new install with MySQL v5.1. I noticed the size of the files and BINEXE increasted by 1,000,000%. Ok, Bulky. Not a problem. I reran the same ODBC test, and now I got: MYSQL v5.1: ~450,000 msecs or 7.5 freaking MINUTES! What the hell happen? Nothing was done. I'm knew to MYSQL. I just installed it with all the defaults. I did choose "developer's machine" for the "optimizer wizard" I can't redistribute MYSQL v3.51 and force it down people's throats! I have to use what they are using already, if already installed. Not even my current system takes 1 minute to add 70,000 records. Why 7.5 minutes? All it is simple inserts/free statements.
View Replies !
What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?
View Replies !
MySQL Slow Log
I have the long query time set to 15 yet MySQL is still showing results with a query time of 0 in the slow query log. It says enter time in "seconds" in the MySQL Administrator but did it mean in milliseconds??
View Replies !
Slow Authentication
MySQL V 5.0.18 on SUSE 10.1 I'm not a complete *nix noob, but I sure as hell ain't a *nix or MySQL pro. This is a new installation. Everything screaming fast. Unless it deals w/authentication. Try to get in w/SQLyog from W2K locally ... intitial connection takes ~20 seconds. Then everything screaming fast. Web Server (W03) attempts to connect via MyODBC ... same result ... initial connection takes ~20 seconds. Subsequent queries screaming fast. VNC into the box at any time ... everything fast. (would seem to eliminate network/connection issues)
View Replies !
Slow Query
i have this query on a website/webapp that has expanded beyond all expectation. It now takes nearly 30secs to return results from the database SELECT cl_t.Client_ID, Buyer_1_Title, Buyer_1_Prename, Buyer_1_Surname, Tel_No, Mob_No, Buyer_2_Title, Buyer_2_Prename, Buyer_2_Surname, Email_Add, Price_Max, MAX(activity_t.Date) AS lastcomm FROM cl_t INNER JOIN cl_want_t ON cl_t.Client_ID = cl_want_t.Client_ID AND Agency_Code ='$agencyloggedincode' AND Deleted = 'N' LEFT JOIN activity_t ON Buy_Sell = 'B' AND Ref_No = cl_t.Client_ID WHERE cl_t.Sales_Agent_ID = $agentid GROUP BY cl_t.Client_ID ORDER BY $order The problem is the call to MAX(activity_t.Date) AS lastcomm activity_t holds all known contact with all known clients and as such is a very large table, the call to search through all of these records and return only the date of the last entry for this client is taking the time. If I remove this from the query I get results in 3 seconds. I have indexing on activity_t.Date & activity_t.Ref_No Question, is there a way of doing this quicker within the table I already have, or should I create another table that just holds the last update date for each client, and get the date from this much smaller table.
View Replies !
Why Is This Query Too Slow?
I find this query to be exceptionally slow(around 2.5 seconds), could some tell me why this is so? MySQL SELECT st.profile_views,count( DISTINCT p.ID ) news_submitted, count( DISTINCT pv.ID ) news_voted, count( DISTINCT pcom.ID ) news_commented, u.joined, u.weight FROM users u LEFT JOIN posts p ON p.submitted_user_id = u.user_id LEFT JOIN post_votes pv ON pv.user_id = u.user_id LEFT JOIN post_comments pcom ON pcom.user_id = u.user_id LEFT JOIN stats st ON st.user_id=u.user_id WHERE u.user_id='john' GROUP BY u.user_id I traced the cause to this line count( DISTINCT p.ID ) news_submitted (from LEFT JOIN posts p ON p.submitted_user_id=u.user_id) But when i execute something like this MySQL SELECT count( DISTINCT p.ID ) news_submitted FROM posts WHERE submitted_user_id='john' it is quite fast (around 0.03 seconds) So why does it slow down when i'm joining the above query with 3 other tables ? Should i use INTEGER for user_id instead of string like 'john'?
View Replies !
How To Log-slow-queries
I'm running MySQL 5.0. Now I would like to log-slow-queries. With MySQL 4 I used mysqld --log-slow-queries --log-long-format start but now I get following note: mysqld: Too many arguments (first extra is 'start'). Use --help to get a list of available options It looks like starting and stopping now only works with etc/init.d/mysql start
View Replies !
Service Pack 4 Slow Down
I'm running Windows 2000 Server and MySQL 4.1 and all has been great until yesterday when I installed Windows Service Pack 4. Now when I query a database it displays very slowly. Before the service pack it was almost instant. Everything works properly, it's just very slow. Anyone heard of anything like this? If I use the MySQL command line and query the database it's very fast. The slow querys are from php pages.
View Replies !
Slow Joining Of Two Tables
I am having trouble combing data from two tables. The tables have exactly the same layout, but have different : mysql> describe MONITORINGUNIT1_DATA; +-------------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+-------+ | dt | datetime | | | 0000-00-00 00:00:00 | | | wg | float(20,3) | YES | | NULL | | | dflag_wg | tinyint(4) | YES | | NULL | | +-------------+-------------+------+-----+---------------------+-------+ mysql> select count(dt) from MONITORINGUNIT1_DATA; +-----------+ | count(dt) | +-----------+ | 24144 | +-----------+ 1 row in set (0.00 sec) mysql> select count(dt) from MONITORINGUNIT2_DATA; +-----------+ | count(dt) | +-----------+ | 1464 | +-----------+ 1 row in set (0.00 sec) Very briefly, [dt] contains an hourly date/time stamp representing when the reading [wg] was taken. [dflag_wg] contains a integer that describes the data (over threshold, under threshold, etc). The DB is populated automatically by a Python script that executes once per hour. If I want to get the overlapping data (with the same date/time stamp) I use this query: Code:
View Replies !
MySQL Slow In Windows NT4
I am running the Apache web server, MySQL v4, and PHP on an NT4 server. Apache runs great, but the auction software I am using (Web2035 Auction software written in PHP) is very, very slow. Sometimes it takes 20-30 seconds to bring up an auction page from the items table which has less than 200 records in it. Can anyone can give me some pointers on where to start looking? (I don't know if the bottleneck is with MySQL or PHP or what I might need to look at to enhance the performance of either package.)
View Replies !
Slow Database Retrieval
iwhat would be the cause of our problem? i don't thnk it has someting to do with the way i run my queries from my applications since i do not encounter such problems on our old machine..Also i tried to execute queries from my sqlyog : ex: select * from db_town result: 8902 rows() in 2687 ms where on the old server it should take only about 421 ms select * from diagnostics_detail result: 42499 rows() in 27609 ms where on the old server it should take only about 4375 ms.
View Replies !
Slow Query Log Not Staying On
I'm running MySQL 4.0.16 on Windows 2003. I just added the mysqld-nt command line option to enable the slow query log, started MySQL, and the option showed up as turned on. Then later I restarted the server, and the slow query log option went back to being turned off. Is this a Windows problem in not remembering the service parameter? Has anyone else seen this?
View Replies !
Slow When Using Desc Clause
I'm scrolling by Order_# where I find and display on screen the < (lesser then) value of Order_#. Here's the syntax: "Select * From Orarchd Where `Order_#` < " & lngOrderNumber & " Order By `Order_#` Desc".... Order_# is the primary index. lngOrderNumber is the current value of Order_#. Here's the problem ...it takes forever to find the next record (at least 10 seconds)
View Replies !
Extremely Slow INSERTs
Im inserting about 4,500 records into a table. There are only three fields each containing text. Im using PHP to insert these records, but it's REALLY slow (can take like 5 minutes to insert line by line. I also tried generating a SQL file and inserting like that, but it still takes a couple of minutes.Is there anythign I should be checking in the mySQL config or something to make this faster??
View Replies !
Connect To A Database Is Slow
I can connect to the mysql server really fast. But when I select a database, the server will wait for about 10 seconds before connecting obviously, imagine that kind of wait on a website. loads of users will click off.
View Replies !
|
TRACKER
