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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
Long Old UPDATE Statement
I have the following SQL which gives me the row Im after SELECT * FROM staff, staffemails WHERE staff.users_username='$user' and staff.staff_id = staffemails.staff_id thing is I now want to perform an update on the result of this query. Ive tried UPDATE staffemails SET is_registered ='0' WHERE staff.users_username = '$user' and staffemails.staff_id = staff.staff_id .
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 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 !
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 !
Lost Connection To MySQL Server During Query After Script Takes 60 Secs
I get the error "Lost connection to MySQL server during query" at the mysql_select_db when reaching this part of the code after 60 seconds of script execuation has already passed. Works fine if the script execution time when reaching this is under 60. Tested with Sleep() and I'm 100% sure that the 60 second barrier is causing it. I just don't know what the barrier is.////
View Replies !
Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null
View Replies !
Specified Key Was Too Long?
when I change the column datatype to varchar[1024], and set it as primary key, mysql 5.0.12 give me a error. Specified key was too long; max key length is 1024 bytes. but how can i increase it, because when i use sql sever 2000, it can work.
View Replies !
Specified Key Too Long ...
I'm migrating a client's cms to a different hosting provider. Their database for the site was originally built on MySQL 4 structure. Their new provider let's me choose between 4.1 and 5.0. I chose 4.1 but have trouble importing it. I'm using phpMyadmin ... and I got this error: #1071 - Specified key was too long; max key length is 1000 bytes
View Replies !
Long Query
Heres my query that will not show up the correct data SELECT * FROM `table` WHERE (`field1`="data1" AND `field2`="data2") AND (`field3` LIKE '%abc%' OR `field4` LIKE '%abc%') It seems not to factor in the first two field's correctly.
View Replies !
Getting Around Long Insert
On my signup page, I have to submit roughly 70 variables to a table. Only a few of these are based on user input, so I was wondering if, when I create the table before I have to worry about this, I can set entries to have a default value (Much like how AUTO INCREMENT works)...
View Replies !
Long Blob
I'm trying to update a longblob field and it throws back an error provided from a mysql class that I've developed in PHP. I'm trying to figure out what I'm doing wrong here. This is my query: UPDATE `orders` SET order='0' WHERE id='16'; order is the longblob field.
View Replies !
#1071 - Specified Key Was Too Long
When I execute this query I get 1071 - Specified key was too long; max key length is 1000 bytes CREATE TABLE `playlists` ( `playlistid` BIGINT NOT NULL AUTO_INCREMENT , `playlisttitle` VARCHAR( 255 ) NOT NULL , `username` VARCHAR( 255 ) NOT NULL , `created` DATETIME NOT NULL , `lastupdated` DATETIME NOT NULL , PRIMARY KEY ( `playlistid` ) , UNIQUE INDEX `playlists_title` ( `playlisttitle` , `username` ) , CONSTRAINT `playlists_username` FOREIGN KEY ( `username` ) REFERENCES `users` ( `username` ) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = InnoDB DEFAULT CHARSET = utf8$$
View Replies !
Connection Needs Too Long
I have a MySQL Server 5.0.21 with a Windows NT 5.0 System and try to connect with en Delphi application through ZEOS-Components over local network. My Problem is that the connect to the WinNT server needs nearly 20 seconds, where a connection to an Windows 2000 system just needs 1 second. Are there any problems that i should know?
View Replies !
Cannot Add Long Text To DB
can anyone help I have built a very basic news feature - which allows users to add news stories to a website. However, the MySQL database doesn't seem to be accepting multiple paragraphs of text it only accepts one or two words. I have altered the field to longtext - but it doesn't make any difference.
View Replies !
When Is A Query Too Long?
I'm just curious to other opinions on the whether you should start to break the sql query in question up, into 2 queries maybe? I have the problem at the moment where I am pulling data from 5 different tables, subqueries and all! I am just not entirely happy with the size of the query and wanted to hear other opinions, Do you try to simplify the query by breaking it up? All tables are normailised (to the best of my ability) and index accrodingly so all that is ok, I am just curious to hear anybody else's thoughts on this.
View Replies !
Long Queries Priority
I have a 120M record table that needs to have indexes added as a background process. While running the CREATE INDEX command, which is taking several days, all other SELECT queries on the same machine (on a different database) are slowed dramatically. Is there a way to reduce the CREATE INDEX priority so that other SELECT queries can still function at normal speed? The OS is linux, and I have tried to 'nice' the caller script, which works for shorter queries, but presumably the longer queries are running entirely in the MySQL process that isn't 'nice'.
View Replies !
MySQL Taking Too Long
I'm trying to run a php script written by someone else, not me, and it's getting stuck in a particular step. Actually it isn't getting stuck per se, but the browser is, because it's taking forever to return the results back to the browser. Here's the line that's responsible for this: $users = $db->query_return_array("SELECT * FROM user"); It's getting stuck because in my database I have over 60,000 records. Now, I'm just wanting to get over this step (it's an upgrade script), not looking for fancy proper methods of php coding. What alternative ways are there for me to prevent the browser from timing out? I'm guessing some way of looping through the records, and updating the client with simple update characters to prevent it from timing out.
View Replies !
Long Blob Select
select * from s_proc where content = 'asiufhasiu' the scrambling field was copied from the control panel of the binary field. Is it possible to do straight query on the control panel directly against the database/tables?
View Replies !
Monitor A Long Insert
Is there a way to know at what stage is a long Insert/Replace query, as how many rows have been inserted? Besides the time counter I've found nothing to do it.
View Replies !
Process Running Long
2874 mysql 24 0 143m 132m 3756 S 0.0 6.5 1296:37 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid- 6 root RT 0 0 0 0 S 1.2 0.0 47:31.78 [migration/2] 3188 mysql 16 0 143m 132m 3756 S 0.0 6.5 9:55.19 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid- Is there any way I can prevent this? This server just has forums on it and thats it. It seems its a runaway process or an orphaned connection. Is there any way to have all processes killed after a certian amount of time like php has in its config file.
View Replies !
Restrictions For Long Sql Queries
are there any restrictions for long sql queries in mysql? I havnt found any info about that. Example: select id from example where id > 1000 and ( ( suche like 'wasser %' or suche = 'wasser,%' or suche = '%,wasser,%' or suche like '%,wasser %' or suche like '% wasser,%' or suche like '% wasser %' or kat1 like 'wasser%' or kat2 like 'wasser%' or kat3 like 'wasser%' or motiv like 'wasser') and ( suche like 'sturm %' or suche = 'sturm,%' or suche = '%,sturm,%' or suche like '%,sturm %' or suche like '% sturm,%' or suche like '% sturm %' or kat1 like 'sturm%' or kat2 like 'sturm%' or kat3 like 'sturm%' or motiv like 'sturm') and ( suche like 'glas %' or suche = 'glas,%' or suche = '%,glas,%' or suche like '%,glas %' or suche like '% glas,%' or suche like '% glas %' or kat1 like 'glas%' or kat2 like 'glas%' or kat3 like 'glas%' or motiv like 'glas') )
View Replies !
Varchar And Long Text
I would like to know that usage of longtext instead of varchar will affect the performance or speed of database? Suppose I am using longtext as datatype for all the alphanumeric fields in a table will that slowdown the database performance? Or is it will be the same as using Varchar for all alphanumberic fields?
View Replies !
Why Does `alter Table` Take So Long?
I'm working with a large table of product orders (6.7 million records), and I've run an Alter Table query to drop an index and three columns. It has been running for about five hours now, but I don't think it has crashed. I'm surprised that it takes so long, after all, I'm simply asking to delete information. Is this typical, or could there be something wrong? Is 6.7 million records simply too much for a single table? If so, what would be a good way to restructure the database to make things better?
View Replies !
Data Too Long For Column
Running 5.0.37-community-nt on windows 2003 server. Having problems getting data to insert into blob table. I receive data too long for column. The file I'm pushing up is 578k. I can do smaller files, like 10k. All this worked until I upgraded from linux to windows and mysql from 4.0.16. Orginally the inserts into this table would cause the entire database to crash. Windows event log showed a DR WATSON error. The mysql error log showed a normal shutdown. Should be noted that this failure actually occured without even naming the blob field name, ie 'INSERT INTO TABLE(ID) VALUES('1')'. I went ahead and just dropped the blob column, added it back with same name, and am now we getting the 'too long for column' message. I've read that this could be a character set issue, however in changing it to what one of the mysql engineers suggested, it still didn't work, and also other queries began failing. ie Code:
View Replies !
Data Too Long With Backslash
I want to insert a row into a table. I use the command "source file.sql" to do that. Into my table, I have a "char" field, with size 16. My problem, is that they are quotation mark, so I must use backslash. Example: INSERT INTO .... VALUES( "test "note" test", ... => If want to insert this, I have this error: ERROR 1406 (22001): Data too long for column 'V_DESC' at row 1 Why mysql count the backslash chars ?
View Replies !
Joins Taking So Long?
Here is my SQL which I am running in MYSQL... select * from acct, custunq where custunq.custno=acct.custno; It should return about 250,000 records. The problem is, it takes about 2 minutes just to return 10,000 records so that would be what... an hour or so just to do this query. I have to be doing something wrong, it only takes like a minute to run within Access, and I thought MySQL was supposed to be faster?
View Replies !
Extremely Long Sql Queries
I have a website based on phpnuke and phpbb (run on mysql). Recently i am experiencing extremely high loads on the server. When i checked the sql records i saw several queries like this one : # User@Host: yehidataf[yehidataf] @ localhost [] # Query_time: 217216307 Lock_time: 0 Rows_sent: 2 Rows_examined: 4 SELECT * FROM nuke_banner WHERE type='0' AND active='1'; When i try to run the query by myself it takes normal amount of time (0.0008 sec or less). What can cause this jump in execution ? my host said it might be caused by an error with php (loop in the code) but that does not seem logical as a loop should create repeated calls to the sql database (shouldnt it) ?
View Replies !
Long Text Display
I am kind of new to php/mysql, I am just now starting to learn some of the basics. I have a website that is dedicated to movies/musicals/plays reviews, and as of now it's not connected to any database, but eventually I would like the information to be stored in mySQL.....
View Replies !
Avoiding Long SQL Statements
I am making queries from php. Some of my SQL statements are getting long. I have some that are six lines long and involve 5 tables or more. I can imagine that my queries are going to get longer. They are starting to be un maintainable. How can I improve the situation?
View Replies !
Storing Long String
If I have a very long string of characters (say 30,000) how would I store this in a mysql table. Obviously I wouldn't use the VARCHAR type but this is the only one I know/am used to. If I have to compress/format the string to store it how would I access the string to do functions on it like read the 15,000th character.
View Replies !
Take Long Time To Run A Query!
I have a webserver that I just use for a webapplication. PHP, Apache and MySQL is installed on the server. The size of MySQL database is 10 GB. End inn it is millions of rows. I wonder where I search what is the normail time the data to return? In my case it takes from 20 sec to 2 min to run a query!
View Replies !
How Check How Long Did My Select Took ?
I have a client for mysql which give me the time it took to query but now is very doubtful. I have 2 machines on the same network accessing the same database one is showing 360ms and the other is showing 5469ms on the same select? Thus I am very confuse where can I verify which is the exact time ? Thanks.
View Replies !
Table Query Taking WAY TO LONG
I have a query that is inner joined with another table based on country codes Select distinct Name.* from Name inner join Location on Location.key = Name.key and Location.cc in ('<list of countries here>'); The problem is when I have more than 2 country codes, the query takes forever... When I show processlist, it says converting HEAP to MyISAM ( this takes over an hour )... I've tried bumping up max_heap_table_size to 128M and tmp_table_size to 128M, but that only seems to delay the converting HEAP to MyISAM message.... Name contains about 3 million records and Location contains about 1.5 million records. Code:
View Replies !
How To Insert A Long Xml String In Mysql
I am trying to insert a long XML string in a blob field in mysql. So i am just using a normal insert statement is there an xml function i could use. This way I do have to keep escaping special characters in the xml string when inserting. e.g i am using insert into table values('test','test','test','xmlstring') Some rows go through but other records do not so does anyone know how i could insert an xml stirng which is everything from an xml file but in string format already.
View Replies !
Restore :: Taking Long Time
I've got a table with over 400,000 records. I did a mysqldump to ensure I could restore it in case of problems. Sure enough, problems arose and I needed to do a restore. The mysqldump created the usual create database, create table and one gigantic insert statement, where all 400,000+ records are inserted. I started my restore around 10pm or so yesterday and was surprised to wake up this morning and find it still running. Considering the dump itself took a couple of seconds, why is the restore taking so long? I'm building a website that's going to have tons of traffic. A database restore that's going to take multiple hours seems to be unacceptable. One way to speed up the process would have been to simply copy the actual database files and just stick them back in the data directory.
View Replies !
|
TRACKER
