mysql is dead slow
Hi, Is something wrong with a sql query like this? (SELECT commentname, count(comments.ID) as comid, 'comments' as section from comments where commentname IN (SELECT DISTINCT commentname from comments where id=35037 ORDER BY commentname)Group by commentname) UNION ALL (SELECT Username, count(ID), 'questions' from questions where Username IN (SELECT DISTINCT commentname from comments where id=35037 ORDER BY commentname)Group by Username) It is dead slow... almost doesnt work.. is it the query or is my DB set up wrong? When i had records of around 500 rows it worked fine.. now i have around 6 rows its dead slowPlease help me...
Re: mysql is dead slow
1, Subqueries should be avoid when using mysql. Rewriting to be using joins will probably solve your problem. 2. Explain is your friend. On Mon, Oct 20, 2008 at 4:38 PM, Sangeetha [EMAIL PROTECTED] wrote: Hi, Is something wrong with a sql query like this? (SELECT commentname, count(comments.ID) as comid, 'comments' as section from comments where commentname IN (SELECT DISTINCT commentname from comments where id=35037 ORDER BY commentname)Group by commentname) UNION ALL (SELECT Username, count(ID), 'questions' from questions where Username IN (SELECT DISTINCT commentname from comments where id=35037 ORDER BY commentname)Group by Username) It is dead slow... almost doesnt work.. is it the query or is my DB set up wrong? When i had records of around 500 rows it worked fine.. now i have around 6 rows its dead slowPlease help me... -- Rob Wultsch
Re: mysql is dead slow
Thanks so much Rob, I will try and use joins and avoid Selects within Selects ...I tried to comment out this select and it works... Gosh I wonder why mysql does not support SEubqueries.. It just hangs in the copying to tmp table status. Atleast I know whats wrong... Thanks very much On Mon, Oct 20, 2008 at 8:06 PM, Rob Wultsch [EMAIL PROTECTED] wrote: 1, Subqueries should be avoid when using mysql. Rewriting to be using joins will probably solve your problem. 2. Explain is your friend. On Mon, Oct 20, 2008 at 4:38 PM, Sangeetha [EMAIL PROTECTED] wrote: Hi, Is something wrong with a sql query like this? (SELECT commentname, count(comments.ID) as comid, 'comments' as section from comments where commentname IN (SELECT DISTINCT commentname from comments where id=35037 ORDER BY commentname)Group by commentname) UNION ALL (SELECT Username, count(ID), 'questions' from questions where Username IN (SELECT DISTINCT commentname from comments where id=35037 ORDER BY commentname)Group by Username) It is dead slow... almost doesnt work.. is it the query or is my DB set up wrong? When i had records of around 500 rows it worked fine.. now i have around 6 rows its dead slowPlease help me... -- Rob Wultsch
Re: mysql is dead slow
On Mon, Oct 20, 2008 at 10:15 PM, Sangeetha [EMAIL PROTECTED] wrote: Gosh I wonder why mysql does not support SEubqueries.. It just hangs in the copying to tmp table status. Atleast I know whats wrong... Thanks very much It supports them, but won't optimize them well until 5.2. There's some good info here: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql is dead slow
On Mon, Oct 20, 2008 at 8:30 PM, Perrin Harkins [EMAIL PROTECTED] wrote: On Mon, Oct 20, 2008 at 10:15 PM, Sangeetha [EMAIL PROTECTED] wrote: Gosh I wonder why mysql does not support SEubqueries.. It just hangs in the copying to tmp table status. Atleast I know whats wrong... Thanks very much It supports them, but won't optimize them well until 5.2. There's some good info here: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ - Perrin I'm pretty sure 5.2 became 6.0 6.0 is two major versions out The last major release happened 3 years ago -- Rob Wultsch
Re: mysql is dead slow
On Mon, Oct 20, 2008 at 9:31 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Mon, Oct 20, 2008 at 8:30 PM, Perrin Harkins [EMAIL PROTECTED] wrote: On Mon, Oct 20, 2008 at 10:15 PM, Sangeetha [EMAIL PROTECTED] wrote: Gosh I wonder why mysql does not support SEubqueries.. It just hangs in the copying to tmp table status. Atleast I know whats wrong... Thanks very much It supports them, but won't optimize them well until 5.2. There's some good info here: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ - Perrin I'm pretty sure 5.2 became 6.0 6.0 is two major versions out The last major release happened 3 years ago What I should have said is: five two is six oh five one comes before six oh five oh three years old -- Rob Wultsch
