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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 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 !
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 !
#1406 - Data Too Long For Column
I want to use a open source software, before the installation. I need to create the database. I can actually, create the database in a good way. When i add the data, i give me '#1406 - Data too long for column 'e_content' at row 1 ' error for the database table gui_element -> 'e_content' field, is set as LongText datatype. I'm not sure this is the limitation of mysql or not. I'm guess it may be some memory problems. Can anyone helps me. i have about 2.00MB sql file, and one of the insert statement caused this error as below: ================================================================================= INSERT INTO gui_element( fkey_gui_id, e_id, e_pos, e_public, e_comment, e_element, e_src, e_attributes, e_left, e_top, e_width, e_height, e_z_index, e_more_styles, e_content, e_closetag, e_js_file, e_mb_mod, e_target, e_requires, e_url ) VALUES ( 'wms_australia', 'content', 2, 1, 'text', 'div', '', '', 10, 50, 500, 30, 2, 'font-family: Arial, Helvetica, sans-serif; font-size : 12px; text-decoration : none; color: #808080;', 'This GUI is an OGC service catalog container and does not provide a map user interface. Please use the (<a href=''javascript:history.back()''>back</a>) link to select another GUI form your list or log off to log on using another account (identity).<br><br>Diese GUI ist ein OGC Service Katalog Beh�lter (Eimer, Container, nimm was Du meinst) und beinhaltet keine Kartenoberfl�che. Verwenden Sie diesen (<a href=''javascript:history.back()''>back</a>) Verweis, um aus der Liste eine andere GUI auszuw�hlen oder melden Sie sich ab, um sich mit einer anderen Kennung anzumelden. ', 'div', '', '', '', '', '' );
View Replies !
Index :: Data Too Long For Column Name In Row XXX
I read that it's preferable to shorten the length of an index if it's based on a varchar. But if I shorten a column defined as: name varchar(40) with an index of: index ix_name (name(10)) and then I try to do an insert into the table a "name" longer than 10 (but less than 40) the insert will fail with the error message "data too long for column name in row XXX". what am i doing wrong? (mysql 5.1)
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 Find All Rows Where Id Is One Of A Long List Of Ids?
I have a long list of "id" values: id1, id2, id3, etc. I want to find all rows in a table where the id is in the list. In other circumstances, I want to find all rows where the id is NOT in the list. Is there a more concise query that the following? SELECT * FROM table WHERE id=id1 OR id=id2 OR id=id3 OR ... For instance, I can't find a syntax that looks like this: SELECT * FROM table WHERE id IN (id1,id2,id3,...) Also, ... if the list of id values is the result of a previous SELECT query, is there a way to save that result and use it in a subsequent query?
View Replies !
Mysqldump Long Format (mysql 5.0)
Does anyone know how to get mysqldump to dump in the old style dump format? Right now I get dumps which look like: INSERT INTO cart VALUES ((a,b,c),(d,e,f),(g,h,i),...,(x,y,z)); What I want is the old INSERT INTO cart VALUES (a,b,c); INSERT INTO cart VALUES (d,e,f); INSERT INTO cart VALUES (g,h,i); ... INSERT INTO cart VALUES (x,y,z); The new style is somewhat problematic when trying to diff database dumps. I don't want to have to edit the dump file to do this. I don't see an option for it offhand.
View Replies !
LONG TEXT Field And HTML Tag
In my article website, I have a database for storing articles. I have defined article body as LONGTEXT in my mySql database. 1st time i submitted an article in plain text using my submit form. it worked fine.. After, i changed all body parts adding some HTML tags which was used to change the looks and make link using URLs, the body part were truncted . only the half of the body was found, dont know what happend to the rest... does LONGTEXT format not support HTML tags? or do i need to make any converstion berore submitting the body field the database?
View Replies !
MySQL Keeps Going Long After Code Stops
When some queries are executed on a server I have setup MySQL will continue to use up CPU cycles 5-10 minutes after the PHP code that executed the query has long since stopped. The browser page will be loaded and no further PHP code will be active on the entire server that could call MySQL into usage, yet MySQL will continue to use upwards of 80-90% of the available CPU processing power for that 5-10 minutes afterward. In fact, if the PHP page only takes a couple of seconds to load with next to no CPU usage then after it has finished loading MySQL will start utilizing the CPU for all it is worth as if it is trying to complete some task that only it knows about. This is on an isolated server so I know I am the only one using it. This seems to happen most often when a LEFT JOIN is executed, though the query itself is very quick and returns the data in less than a second. I am running MySQL 5.0.15 with Apache 2.0.54 and PHP 5.0.4 on Windows XP SP2. I know this is terribly obscure sounding, but if anyone has any ideas where I could start to look for the source of the problem I would appreciate it. I know it is not in the code because if I run the code on a Linux server this doesn't happen, not to mention the fact that, as I said, the PHP code itself finishes without a hitch.
View Replies !
How Long Would It Take The Production Server To Add New Indexes?
Below is the table structure. How long would it take to add two new indexes to this table that has 200,000 records? I know it partially depends on what else the server is doing and the type of machine but will it take hours or seconds is my basic question? CREATE TABLE `purchase_list` ( `purchase_list_id` int(10) unsigned NOT NULL default Ɔ', `purchase_id` int(10) default Ɔ', `menu_id` int(10) unsigned NOT NULL default Ɔ', `purchase_list_price` float NOT NULL default Ɔ', `purchase_list_specific` varchar(128) default NULL, `purchase_list_name` varchar(255) NOT NULL default '', `user_id` int(10) default NULL, `old_purchase_id` int(10) default NULL, `purchase_mod_date` datetime default NULL, PRIMARY KEY (`purchase_list_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
View Replies !
Long Text Into A MySQL Field Error
I am having a problem putting a certain length of text from a textarea form element into a mySQL field. The field is set to LONGTEXT but I get an error. Whne I shorten the text it writes to the DB with no problem. I am getting a long strng, perhaps 200 characters or more into a variable that will then be written to an mySQL DB but I am getting an error in the DB. How can I set the field to accept more than 400 or 700 text characters?
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 !
Special Characters :: Data Too Long For Column Xxx ...
I'm new working with mySQL and I'm having trouble dealing with special characters... Every time I try to insert data on the database containing special characters (i.e. "não" ou "já") the dbms returns the error: "Data too long for column 'xxx' at row n.". I use MySQL 5.0 and Windows XP SP2 Pro. Here are the values of the variables like "character%" and "collation%" (The charset directory is ok, only the file utf8.xml is not in there, but it's compiled into the dbms so I think that´s not a problem... hope I'm not wrong): Code:
View Replies !
|
TRACKER
