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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Indexing Taking Long Time
I am currently importing about a million rows into temp tables - then the temp tables are indexed and finally the original tables are dropped and the temp tables are renamed - The indexing of the temp tables takes a considerable amount of time - My question is - Would there be a difference if i index the temp table before importing as opposed to after??
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
How To Prevent MySQL Queries From Taking Too Long
I have a very busy database with some tables having hundreds of thousands of records. I am running MySQL 5.0.16 on Windows. Some of the queries running on my database are not fined-tuned enough, and need improvement. However, the system is live in production and I am looking for a way to prevent MYSQL from committing Harakiri, i.e. from running very slow queries forever, taking the cpu to 50% and beyond for hours. My scripting language is PHP. I found that after a while the end user receives a timeout on the browser, but MySQL continues trying to run the query, sometimes for hours. My question is, is there a way to specify a timeout for MySQL so that it will abort any queries that it is running that have not returned a result by then. The end user is no longer around anyway, so I am looking for a way to protect the database resources (and in parallel I will start investigating how to optimize the slow queries).
View Replies !
View Related
Optimizing Table - Count Taking Too Long
Could anyone help me with some advise on optimizing a table? It currently has 600,000 records and 'select count(*) from products where 1' is taking several seconds to return a result. Mysql is running locally on my development machine (XP) as a service and is version 5.0.24a The table is defined as follows: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `description` text, `taxable` tinyint(1) default Ɔ', `sub_title` varchar(255) default NULL, `isbn` varchar(13) default NULL, `format` varchar(40) default NULL, `published_date` datetime default NULL, `pages` int(11) default NULL, `refundable` tinyint(1) default NULL, `refund_by_date` datetime default NULL, `publisher` varchar(255) default NULL, `bic_classification` varchar(10) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_products_on_isbn` (`isbn`), KEY `index_products_on_name` (`name`), KEY `index_products_on_bic_classification` (`bic_classification`), KEY `index_products_on_publisher` (`publisher`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
Script Taking Ages
if you can help me below is the script i run Code: select *,concat('[',first,'][',last,'][',postcode,']') from `new` where concat('[',first,'][',last,'][',postcode,']') not in (select concat('[',first,'][',last,'][',postcode,']') from `already`) basically thids is what happens it just takes too long and wanted to speed it up alot New = 70k records Already = 65k records i need to be left with the 5k difference
View Replies !
View Related
Stopping MySQL Taking Down A Server?
This may be a silly question but what can be done to stop load-intensive MySQL processes taking down a server? Things like adding fulltext indexes to very large tables, or selects on very large (multi-million-row) tables just completely kill the box until they complete. I don't mind how long these things take but the box shouldn't become unresponsive while they are running.
View Replies !
View Related
HABTM Join Taking Ages
I have 3 tables: profiles (Contains User Profiles - 4 Million records) sections (Self Referencing Table containing Genres, Cinemas, Communication Preferences etc - 40 Million records) profiles_sections (HABTM between profiles and section - 200 Records) For example, I want to select all profiles in the database where the profile has select 3 sections. Currently, the only way I can get this data is with a VERY intense query which takes ages to return any records: ...
View Replies !
View Related
Taking Colomns Of Data And Copying It To A Different Table
I've got two pieces of software running and I want to force users to only use the one log in so I though rather than trying to build a plug in I thought a cron job would no-doubt be easier. Now getting the data I want from the right table is easy enough for me but as the columns have different ID's I need to make it transpose it into the right columns... It runs but I suspect the second part of my code is failing. Code:
View Replies !
View Related
Float Field Not Taking Dots, Just Commas
I'm trying to add google maps coordinates to a float field, but I think due to my character coding it's not taking dots, and the coordinates come in a format like "-12.345678" it's a 10 character float with 6 decimals. What should I do to use a dot as a separator and not a comma?
View Replies !
View Related
Nested Select Statement Taking Hours To Execute
I am running a sub select statment so I can compare a couple of tables. If I run the statement from two test tables of 1000 records in each, it will take nine seconds to get the results that I need. However, if I take the same statement and point it to the two tables which contain all the data I need to run against (50560 and 161680 records respectively), it takes hours to run...how long I'm not sure. I fired the statement off yesterday afternoon @ 3pm and it is currently 11:30am the following day...the select statement is still executing. Does anyone have performance on something like this?
View Replies !
View Related
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
View Related
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
#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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
|
TRACKER
