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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
SUBQUERY Vs DEPENDENT SUBQUERY
I have a table called `accounts`, that contains columns: account_name VARCHAR (128) account_no VARCHAR (16) account_hash VARCHAR (32) balance DECIMAL(10,2) There is an INDEX on account_hash and there's currently 128 rows in the table. I have a table called `activity` that contains columns:....
View Replies !
Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query. First Query select ACCOUNT_ID, ACCOUNT_TYPE_C, PAT_ID from PAT_ACCT_CVG where ACCOUNT_TYPE_C in (120103,120104,120101) Second Query SELECT PAT_CVG_FILE_ORDER.PAT_ID, PAT_CVG_FILE_ORDER.LINE, COVERAGE.COVERAGE_ID, COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT FROMPAT_CVG_FILE_ORDER LEFT OUTER JOIN COVERAGE ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID Where coverage.payor_id = ?' Third Query select TRAN.ORIG_SERVICE_DATE TRAN.TRAN_TYPE, TRAN.INSURANCE_AMOUNT from Tran where TRAN.TRAN_TYPE = 1 and TRAN.INSURANCE_AMOUNT > 0 and TRAN.proc_ID in 1008,1009 (now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)
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 !
Sum() And Subquery
SELECT domainTable.name as domain, sum(NetworkTraffic.upload) as Upload, sum(NetworkTraffic.download) as Download, sum(NetworkTraffic.upload) as Upload2 sum(NetworkTraffic.download) as Download2 FROM `NetworkTraffic` LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId WHERE NetworkTraffic.protId=2 AND date between "2005-10-11" AND "2005-10-18" GROUP BY domainTable.name I am using the left-join above to merge two tables (NetworkTraffic and domaintable) on domainId to find which domain to present. By doing this i summarize the download and upload from NetworkTraffic between 2005-10-11 and 2005-10-18, however i want to be able to summarize the traffic from one day, say 2005-10-12 in column 3 and 4 (Download2, Upload2). Is it possible to make a subquery in the sum-function or should i take another approach? I am using mysql server 4.0.18-max-debug
View Replies !
LIKE ANY + Subquery
I'm trying to get the following statement to work: SELECT * FROM discountItems di WHERE di.name LIKE ANY (SELECT lsw.word FROM ifDefinedSearchWords dsw join ifLinkedSearchWords lsw on lsw.fIFEntityID = dsw.fIFEntityID WHERE dsw.word like 'schoggi') It is supposed to find some words in a subquery as one row, and then search another table for records matching any of those words. I get the following error message: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near .....
View Replies !
Subquery Help
I am using mysql 4.0 which does not support subquerys.How can i rewrite the below query using joins for mysql 4.0 select * from t1 where t1.eid not in(select eid from t2)
View Replies !
Subquery Help.
I am using the following but then I rembered I am getting the readyPrinted_id from the select statement!! How can I fix it so it gets the readyPrinted_id and then performs the AND with that same ID? SELECT `sub_name` , `readyPrinted_name` , `readyPrinted_id` FROM sub s, readyPrinted r WHERE s.sub_id = $sub_id AND s.readyPrinted_id = r.readyPrinted_id AND r.readyPrinted_id = readyPrinted_id
View Replies !
Request Help For Subquery
I have a table that records targets and the time it appears on a display. What I would like to do is to report the time difference for each individual target from the initial appearance to the subsequent one, and the time difference from the subsequent one to the next, and so on. So how do I put these these all together to produce one query: for each "select distinct target from display" for number of rows -1 with target "select timeX - timeY from (subquery for distinct target) where (X,Y = subsequent, initial times)"
View Replies !
How To Do A Subquery With MySQL 4.0 ?
I couldn;t upgrade to mysql 4.1 for some reason, so I am forced to use mysql 4.0. I try to change my database from pgSQl to mySQL. But the proble is how to make a query like : "SELECT field FROM table WHERE (select count(*) from table2)<5 " which is very easy under pgSQL but not allowed under mysql because the subqueries seem not to be allowed...
View Replies !
Subquery (not Exists)
I've got some sort of syntax problem that doesn't seem to make a lot of sense. I'm developing a Categories Theory application and because of that I need to make big, and by "big" I mean HORRIBLY HUGE queries. That one has 54 lines and 3 subqueries (only the first one is shown so that I won't scare people off :)). The thing is, I don't seem to be getting the hang of how to do subqueries. The syntax seems fine... But it'll still always say the same thing: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists ( select * from objeto c, morfismo f1, morfismo f2 (It's MySQL 4.0.11a-gamma) The only difference I see between the code below and the mysql.com documentation is that my subqueries aren't alone in their "where" clauses... Aside from that, they seem pretty much okay. Am I missing something?.....
View Replies !
Correlated Subquery
SELECT MAX(e1.score) AS high_score , e1.dept , (SELECT e2.emp_no FROM Employee_Evaluations AS e2 WHERE e2.dept = e1.dept AND e2.score = MAX(e1.score)) AS emp_no FROM Employee_Evaluations AS e1 GROUP BY e1.dept It doesn't work in MySQL (I've tried 4.1 and 5.0), but it seems like it should work and if I'm not mistaken, it's valid SQL99. Can anyone confirm this? I don't need help writing a compatible version, I've already done that - just curious if this little bugger is standards compliant.
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 !
Subquery As Join
I have the following query, which works perfectly on my developpement computer: UPDATE writings SET num_votes=(SELECT COUNT(vote) FROM votes WHERE writing_id=id), rating=(SELECT AVG(vote) FROM votes WHERE writing_id=id); Unfortunately, my host doesn't seem to allow subqueries. Is there a way to do this with joins? I've heard that joins are faster anyhow.
View Replies !
Subquery Issue
I have a query at sql.pastebin.com/d3398502e and it's a bit of a monster (sorry, it says I can't post the actual link because I'm a new user, I assume it's a spam precaution, so please copy and paste). It's a search query for a hotel site, like expedia, but it needs the ability to get different prices for children in a room based on their age. Each hotel can have age groups, so it needs to figure out how many children are in each age group for that hotel (which works, that's all the UNIONs), and then apply the price associated with that age group to the number of children for that hotel for that date. That's where I'm stuck since the price data for that date is not available in the subquery. The way I have it now it's selecting all dates that have child prices applied and adding those together, rather than just the one date.
View Replies !
Subquery To JOIN
SQL Code: Original - SQL Code SELECT COUNT(*) FROM `cmn_group` AS `g` INNER JOIN `cmn_company` AS `c` ON g.cmn_company_id = c.cmn_company_id WHERE g.cmn_group_id !=5 AND g.group_name = 'G4' AND g.cmn_company_id = ( SELECT cmn_company_id FROM cmn_group WHERE cmn_group_id =5 )  SELECT COUNT(*)FROM `cmn_group` AS `g`INNER JOIN `cmn_company` AS `c` ON g.cmn_company_id = c.cmn_company_idWHERE g.cmn_group_id !=5AND g.group_name = 'G4'AND g.cmn_company_id =( SELECT cmn_company_id FROM cmn_group WHERE cmn_group_id =5)
View Replies !
Using Limit In Subquery
I am trying to use Limit in a subquery but receive an error. Is there another way to archive the same end result without Limit? SELECT * FROM news WHERE news.id NOT IN (SELECT news.id FROM news ORDER BY date DESC LIMIT 15) Error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
View Replies !
Update With Subquery?
I have two tables and I want to update one with data from the other TableA id description date TableB id counter date I want to update counter in TableB with its current value minus a count from tableA UPDATE TableB set counter = counter - (SELECT count(id) WHERE description = 'blabla') But I only want to do this on the date columns are equal. For example date in TableB is 2007-03-13, and counter should be updated with its value minus the selection from TableA where date is the same. But I want to do this on all rows for all dates in one query. How do I do that? Add a where to the subquery ie "WHERE TableB.date = TableA.date"? Is that correct?
View Replies !
SubQuery To Add A Column
I have the following SQL which is the best I can do to illustrate what I am trying to accomplish: select column_name as 'name', column_default as 'default', column_type as 'type', (select column_name from maillists where listname="winserver") as 'data' from information_schema.columns where table_name='maillists'; Essentially, I am trying to get a "description" of my table along with an additional column of the current value for each field in the table. What I am getting instead is a repeat of the column name in the data column....
View Replies !
Subquery In COUNT()
I have table with names and ages of peoples. I need to select ALL names and number of younges peoples. name|age aaa |30 bbb |31 ccc |32 eee |32 result: aaa |0 bbb |1 ccc |2 eee |2 I try use: SELECT T1.name, COUNT(SELECT * FROM tablename AS T2 WHERE T2.age<T1.age) FROM tablename AS T1; But error in syntax :(
View Replies !
Error While AVG On A Subquery
I have a ticket_audit_log table that is a list of changes done on tickets in the ticket table. Every change is a timestamp, so i can select the min and max timestamp for each ticket change and calculate the difference that is the time the ticket has been worked on. The query I use is: ....
View Replies !
Using IN With An Empty SUBQUERY
UPDATE bb_topics SET topic_status = 1 WHERE topic_id IN (SELECT topic_id FROM bb_topics WHERE forum_id = 1 AND topic_start_time < DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND topic_status = 0); If the subquery returns an empty dataset I get a syntax error. Is there any way to test if the subquery is null or exist. I have tried EXISTS and IFNULL with no success.
View Replies !
Subquery Using Limit
I am trying to use a limit 1 in a subquery which its returning a error saying its not supported. Basically I have a table for Locations where equipment has been and I need to run one query that returns only the last location that the equipment has been. The below query is returning any of the ID numbers where the current location = 232. This does return results but it also returns results where 232 is one of the previous locations. A simple fix would be to use LIMIT 1 in the subquery but since that isnt supported .....
View Replies !
Subquery As An Array?
Let us say I have two tables: - user: userid, username - post: postid, userid In a single query, given the "userid" value, I want to select "username" from the user table, and select all postid values from the post value where the userid="myuserid". Something like... SELECT username, (SELECT postid FROM post WHERE userid="myuserid") AS postids FROM user WHERE userid="myuserid" I'm sure you can spot the problem--the subquery returns more than one row of data. Is there any way I can make "postids" be an array, or a comma-separated list, or something like that?
View Replies !
Subquery With IN Clause
I have a table full of subscriptions to a service. this is our second year, and i want to see how many people returned for a new season. I am having a hard time finding a solution to this problem. so far i have this... SELECT count(*) FROM product WHERE DATE_FORMAT(insert_date,"%Y")='2005' AND payer_email IN (SELECT DISTINCT payer_email FROM product WHERE DATE_FORMAT(insert_date,'%Y')='2004') but the page just never loads when i run that. Does anyone have any ideas as to what's wrong with my query, or a better query i might make?
View Replies !
DELETE And Subquery
Here is my DELETE statement that should work but doesn't due to "Currently, you cannot delete from a table and select from the same table in a subquery. " DELETE FROM history WHERE eventTime = (SELECT MIN(eventTime) from history) I found information about selecting the max with a subquery here:
View Replies !
MySQL Subquery Using NOT IN
I need a little help rewriting a subquery in a MySQL db that does not support this type of query. I have read the manual about how to convert a NOT IN to a left join and it makes good sense, but I have on small addition to it that I cannot get to work. The manual states that this is the correct SQL for a NOT IN subquery:
View Replies !
Row Subquery In Fieldlist
I am trying to figure out how to shorten my SQL. I know I can do this: SELECT (SELECT some_single_field FROM somewhere) AS field_alias FROM parent_table But I want to be able to use the subquery to return 2 values - something like this, (pseudo-code): (SELECT some_field, some_other_field FROM somwhere) AS field_1, field_2 I know how to do comparisons in the WHERE clause using multiple columns, but I can't seem to figure it out in the beginning of the select. Does anyone know how this is done?
View Replies !
Using Union In A Subquery?
I'm trying to make a query that fetches messages that were created by one of your friends (friendships are stored in a separate table) and was thinking this query would possibly do the trick: SELECT * FROM public_messages WHERE author_id IN ( (SELECT friend_from FROM friendships WHERE friend_to=1 AND pending=0) UNION (SELECT from_to FROM friendships WHERE friend_from=1 AND pending=0) ) Where the user's id is 1... Running this query gives an error of: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ( SELECT from_to FROM friendships WHERE friend_from = 1 AND pending = 0 ) ' at line 1 and of course it works fine without the union... Is such a thing possible in one query?
View Replies !
Making The Right Subquery
I've been trying to wrap my head around this one for a while, and while it would be pretty easy to do using a loop in PHP I'd like to keep it in a single SQL statement if possible. I have a table of comments, very much like you would store comments in for a blog. It goes something like this: idCOMMENT | FK_LIST_ID | COM_Posted | COM_Comment autoincrement| foreign key of post | Timestamp | Text for Comment I only want to keep the most recent 30 or so comments on each listing so I need to create a statement that will delete any excess comments. I could loop a statement like this in PHP: DELETE FROM COMMENT WHERE idCOMMENT IN (SELECT idCOMMENT FROM COMMENT WHERE FK_LIST_ID=(variable from PHP) ORDER BY COM_Posted Desc LIMIT 30, 10^10) But there will be thousands of listings, (it's and that's going to be rough on the server to make thousands of calls to it each night with the maintenance script. I think there must be a way to do this in pure SQL, just send one SQL statement to the server and that's it, but can't figure it out. Can you point me in the right direction?
View Replies !
|
TRACKER
