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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
Error Pasting Long Query Into Terminal
mysql database < /location/of/statement.txt Is there a limit to the number of lines a statement can have in a terminal window? OK, so I just tried the same query on another server and it works in that terminal window? Is there a clip board or cache that needs to be cleared from mysql every once in a while? Why would it work on one and not on another.
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 !
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 !
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 !
Null Field Not Taking Data
I have a mysql table with a password field. (varchar(20), Null YES). When i input data into the field from the form, I look at the table and it say null. It's not taking the data that I input into it.
View Replies !
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 !
Putting In And Taking Out True/false Instead Of 1/0
In mysql 5.0, as far as I understand it, BOOLEAN is an alias for tinyint(1) How far does that aliasing go? When you INSERT, does it have to be a 1 or 0, or can it be a true/false? Same with SELECT, does it have to return 1/0, or can it be true/false? If it matters, I'm working with PHP.
View Replies !
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 !
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 !
Performance Issue 2217728 Records :: Selects Taking Half An Hour
I am trying to execute following statement, which is not getting completed even after half an hour. SELECT FIND_IN_SET(t.seq, (SELECT GROUP_CONCAT(xx.seq) FROM indiv xx WHERE xx.unique_id = t.unique_id)) FROM indiv t indiv table has around 2217728 records. I have already index on unique_id. What should i do to speedup this statement?
View Replies !
Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP $gettray = mysql_query("SELECT trailers.title, trailers.link, trailers.movie, movie.title AS mtitle FROM trailers,movie WHERE trailers.movie=movie.word ORDER BY trailerid DESC LIMIT 6",$connm); It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table. I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet. If the row does not exist in 'movies', the program than uses the entire trailer title like so PHP if($ttray['mtitle']) { $newttitle = explode("-",$ttray['title']); $newttitle = array_reverse($newttitle); $ttitle = $newttitle[0]; $ttitle = $ttray['mtitle'] ."- ". $ttitle; } else { $ttitle = $ttray['title']; } Thanks Ryan
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
|
TRACKER
