RE: Subquery taking too much time on 5.5.18?
How fast does this run? SELECT A.* FROM A JOIN B ON B.A_ID = A.id WHERE B.name LIKE 'X%'; Turning a subquery into a JOIN usually improves performance. (The main exception is when the subquery consolidates data via GROUP BY, DISTINCT, LIMIT, etc.) > -Original Message- > From: Cabbar Duzayak [mailto:cab...@gmail.com] > Sent: Friday, July 06, 2012 8:46 AM > To: mysql@lists.mysql.com > Subject: Subquery taking too much time on 5.5.18? > > Hi Everyone, > > I have been trying to understand why subqueries are taking tooo much > time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. > > In a nutshell, I have 2 tables: A and B. And, I do something like this: > > SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like > 'X%'); > > Table A has 460,000 rows and Table B has 5000 rows, and A.id is my > primary key, and B.name is indexed. Also, the sub-query here (B.name > starts with X%) returns about 300 rows. > > For some weird reason, this query takes a ton of time (I cancelled it > after 750 seconds). I looked at the query plan with EXPLAIN and it > could not find an index to use for table A and looks like it is doing a > table scan (even though A.id is the primary key)... > > To understand it better, I divided it up, and sent two queries > separately as follows:: > > "SELECT A_ID FROM B WHERE B.name like 'X%'" > takes 0.002 second. > > For testing purposes, I concatenated all ids from this query and send a > hard-coded query on A like: > > SELECT * FROM A WHERE A.id in (1,2,3,4,5.) and this takes 0.002 > second. > > > > Basically, both queries are super fast, but when I combine them via IN > w/sub-query, the thing spends a lot more time? > > > As an alternative, I tried using JOIN as follows: > SELECT A.* FROM A INNER JOIN B ON (A.id = B.A_id) WHERE B.name like > 'X%'; and this takes 0.04 seconds > > JOIN is also fast, but there are cases where I really need IN > subqueries. > > > I would really really appreciate it if you can shed some light on this > issue and tell me what I am doing wrong and/or how I can fix this? > > Thanks a ton. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Subquery taking too much time on 5.5.18?
> query B can not used any key because 'like' never can use any key Not true. LIKE without a leading wildcard is optimized like a BETWEEN. > -Original Message- > From: Reindl Harald [mailto:h.rei...@thelounge.net] > Sent: Friday, July 06, 2012 8:58 AM > To: mysql@lists.mysql.com > Subject: Re: Subquery taking too much time on 5.5.18? > > > > Am 06.07.2012 17:46, schrieb Cabbar Duzayak: > > Hi Everyone, > > > > I have been trying to understand why subqueries are taking tooo much > > time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. > > > > In a nutshell, I have 2 tables: A and B. And, I do something like > this: > > > > SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like > > 'X%'); > > > > Table A has 460,000 rows and Table B has 5000 rows, and A.id is my > > primary key, and B.name is indexed. Also, the sub-query here (B.name > > starts with X%) returns about 300 rows. > > query B can not used any key because 'like' never can use any key > > i try to avoid subqueries wherever i can becasue the mysql query- > optmizer is really weak in most cases (it appears > 5.6 will be much better in many of them) > > such things i would always do with two queries in the application > > * first the sub-query > * genearte the query above with the results in the app > * fire up the final query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Subquery taking too much time on 5.5.18?
mysql> SELECT @@optimizer_switch; ++ | @@optimizer_switch | ++ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on | ++ 1 row in set (0.00 sec) On Sat, Jul 7, 2012 at 8:01 PM, Peter Brawley wrote: > On 2012-07-07 9:52 AM, Cabbar Duzayak wrote: >> >> Hmm, >> >> Looking at the link http://www.artfulsoftware.com/infotree/queries.php >> and explanations here, EXISTS() should have performed better, but does >> not seem to??? I stopped it after about 5 minutes. >> >> I tried both: >> >>SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and >> B.name like 'X%'); >> >> and >> >>SELECT * FROM A WHERE EXISTS (SELECT B.A_ID FROM B WHERE A.id=B.A_ID >> and B.name like 'X%'); >> >> Both are slow... > > > What's the optimizer_switch setting? > > PB > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Subquery taking too much time on 5.5.18?
On 2012-07-07 9:52 AM, Cabbar Duzayak wrote: Hmm, Looking at the link http://www.artfulsoftware.com/infotree/queries.php and explanations here, EXISTS() should have performed better, but does not seem to??? I stopped it after about 5 minutes. I tried both: SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name like 'X%'); and SELECT * FROM A WHERE EXISTS (SELECT B.A_ID FROM B WHERE A.id=B.A_ID and B.name like 'X%'); Both are slow... What's the optimizer_switch setting? PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Subquery taking too much time on 5.5.18?
Hmm, Looking at the link http://www.artfulsoftware.com/infotree/queries.php and explanations here, EXISTS() should have performed better, but does not seem to??? I stopped it after about 5 minutes. I tried both: SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name like 'X%'); and SELECT * FROM A WHERE EXISTS (SELECT B.A_ID FROM B WHERE A.id=B.A_ID and B.name like 'X%'); Both are slow... On Sat, Jul 7, 2012 at 8:34 AM, Rik Wasmus wrote: >> See "The unbearable slowness of IN()" at >> http://www.artfulsoftware.com/infotree/queries.php > > > Do you read your own links? Excerpt: > >> In 5.0, EXISTS() is much faster than IN(), but slower than JOIN. In 5.5, > EXISTS() performs about as well as JOIN. > > So judging by the subject line... > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Subquery taking too much time on 5.5.18?
> See "The unbearable slowness of IN()" at > http://www.artfulsoftware.com/infotree/queries.php Do you read your own links? Excerpt: > In 5.0, EXISTS() is much faster than IN(), but slower than JOIN. In 5.5, EXISTS() performs about as well as JOIN. So judging by the subject line... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Subquery taking too much time on 5.5.18?
On 2012-07-06 5:07 PM, David Lerer wrote: Cabbar, try to replace the IN subquery with an EXISTS. Something like: SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name like 'X%'); See "The unbearable slowness of IN()" at http://www.artfulsoftware.com/infotree/queries.php SELECT a.* FROM a JOIN b USING(a_id) WHERE B.name LIKE 'X%'; PB - Does it help? David. -Original Message- From: Cabbar Duzayak [mailto:cab...@gmail.com] Sent: Friday, July 06, 2012 11:46 AM To: mysql@lists.mysql.com Subject: Subquery taking too much time on 5.5.18? Hi Everyone, I have been trying to understand why subqueries are taking tooo much time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. In a nutshell, I have 2 tables: A and B. And, I do something like this: SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like 'X%'); Table A has 460,000 rows and Table B has 5000 rows, and A.id is my primary key, and B.name is indexed. Also, the sub-query here (B.name starts with X%) returns about 300 rows. For some weird reason, this query takes a ton of time (I cancelled it after 750 seconds). I looked at the query plan with EXPLAIN and it could not find an index to use for table A and looks like it is doing a table scan (even though A.id is the primary key)... To understand it better, I divided it up, and sent two queries separately as follows:: "SELECT A_ID FROM B WHERE B.name like 'X%'" takes 0.002 second. For testing purposes, I concatenated all ids from this query and send a hard-coded query on A like: SELECT * FROM A WHERE A.id in (1,2,3,4,5.) and this takes 0.002 second. Basically, both queries are super fast, but when I combine them via IN w/sub-query, the thing spends a lot more time? As an alternative, I tried using JOIN as follows: SELECT A.* FROM A INNER JOIN B ON (A.id = B.A_id) WHERE B.name like 'X%'; and this takes 0.04 seconds JOIN is also fast, but there are cases where I really need IN subqueries. I would really really appreciate it if you can shed some light on this issue and tell me what I am doing wrong and/or how I can fix this? Thanks a ton. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Subquery taking too much time on 5.5.18?
Cabbar, try to replace the IN subquery with an EXISTS. Something like: SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.id=B.A_ID and B.name like 'X%'); Does it help? David. -Original Message- From: Cabbar Duzayak [mailto:cab...@gmail.com] Sent: Friday, July 06, 2012 11:46 AM To: mysql@lists.mysql.com Subject: Subquery taking too much time on 5.5.18? Hi Everyone, I have been trying to understand why subqueries are taking tooo much time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. In a nutshell, I have 2 tables: A and B. And, I do something like this: SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like 'X%'); Table A has 460,000 rows and Table B has 5000 rows, and A.id is my primary key, and B.name is indexed. Also, the sub-query here (B.name starts with X%) returns about 300 rows. For some weird reason, this query takes a ton of time (I cancelled it after 750 seconds). I looked at the query plan with EXPLAIN and it could not find an index to use for table A and looks like it is doing a table scan (even though A.id is the primary key)... To understand it better, I divided it up, and sent two queries separately as follows:: "SELECT A_ID FROM B WHERE B.name like 'X%'" takes 0.002 second. For testing purposes, I concatenated all ids from this query and send a hard-coded query on A like: SELECT * FROM A WHERE A.id in (1,2,3,4,5.) and this takes 0.002 second. Basically, both queries are super fast, but when I combine them via IN w/sub-query, the thing spends a lot more time? As an alternative, I tried using JOIN as follows: SELECT A.* FROM A INNER JOIN B ON (A.id = B.A_id) WHERE B.name like 'X%'; and this takes 0.04 seconds JOIN is also fast, but there are cases where I really need IN subqueries. I would really really appreciate it if you can shed some light on this issue and tell me what I am doing wrong and/or how I can fix this? Thanks a ton. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Subquery taking too much time on 5.5.18?
Benjamin: I tried this, but it is slow as well, but it went down to something like 20 seconds. Reindl: I was thinking about doing this on the app server side, but it consumes more memory + requires multiple roundtrips, that is why I wanted to do it on the mysql side. Also, like uses the index if index is not hash, and if cardinality value is more appropriate, ie. your predicate cardinality is better with the index instead of table scan and if like does not start with a percent. Also, JOIN works if you have 2 tables involved, but it starts getting messier and messier as you keep converting every IN to JOINs, and I was thinking that optimizer should be able to optimize this much better and faster than JOINs. Thanks. On Fri, Jul 6, 2012 at 7:37 PM, Stillman, Benjamin wrote: > As far as I know, a B-tree index can be used by LIKE as long as the string > doesn't begin with a wildcard. " LIKE 'X%' " should be fine to use an index > on the name column. The index only includes results in the search base which > start with X. > > That said, I probably wouldn't use a subquery, either. But since the OP says > they'd prefer to use subqueries, try this and tell me what happens: > > SELECT * FROM A WHERE A.id IN ( > SELECT A_ID FROM ( > SELECT A_ID FROM B WHERE B.name LIKE 'X%' > ) AS x > ); > > It's just wrapping the subquery within another subquery, forcing MySQL to run > from the inside out. I don't have a running instance nearby to test on, but I > hope it helps. > > > > > > > -Original Message- > From: Reindl Harald [mailto:h.rei...@thelounge.net] > Sent: Friday, July 06, 2012 11:58 AM > To: mysql@lists.mysql.com > Subject: Re: Subquery taking too much time on 5.5.18? > > > > Am 06.07.2012 17:46, schrieb Cabbar Duzayak: >> Hi Everyone, >> >> I have been trying to understand why subqueries are taking tooo much >> time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. >> >> In a nutshell, I have 2 tables: A and B. And, I do something like this: >> >> SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like >> 'X%'); >> >> Table A has 460,000 rows and Table B has 5000 rows, and A.id is my >> primary key, and B.name is indexed. Also, the sub-query here (B.name >> starts with X%) returns about 300 rows. > > query B can not used any key because 'like' never can use any key > > i try to avoid subqueries wherever i can becasue the mysql query-optmizer is > really weak in most cases (it appears > 5.6 will be much better in many of them) > > such things i would always do with two queries in the application > > * first the sub-query > * genearte the query above with the results in the app > * fire up the final query > > > > > Notice: This communication may contain privileged and/or confidential > information. If you are not the intended recipient, please notify the sender > by email, and immediately delete the message and any attachments without > copying or disclosing them. LBI may, for any reason, intercept, access, use, > and disclose any information that is communicated by or through, or which is > stored on, its networks, applications, services, and devices. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Subquery taking too much time on 5.5.18?
As far as I know, a B-tree index can be used by LIKE as long as the string doesn't begin with a wildcard. " LIKE 'X%' " should be fine to use an index on the name column. The index only includes results in the search base which start with X. That said, I probably wouldn't use a subquery, either. But since the OP says they'd prefer to use subqueries, try this and tell me what happens: SELECT * FROM A WHERE A.id IN ( SELECT A_ID FROM ( SELECT A_ID FROM B WHERE B.name LIKE 'X%' ) AS x ); It's just wrapping the subquery within another subquery, forcing MySQL to run from the inside out. I don't have a running instance nearby to test on, but I hope it helps. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, July 06, 2012 11:58 AM To: mysql@lists.mysql.com Subject: Re: Subquery taking too much time on 5.5.18? Am 06.07.2012 17:46, schrieb Cabbar Duzayak: > Hi Everyone, > > I have been trying to understand why subqueries are taking tooo much > time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. > > In a nutshell, I have 2 tables: A and B. And, I do something like this: > > SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like > 'X%'); > > Table A has 460,000 rows and Table B has 5000 rows, and A.id is my > primary key, and B.name is indexed. Also, the sub-query here (B.name > starts with X%) returns about 300 rows. query B can not used any key because 'like' never can use any key i try to avoid subqueries wherever i can becasue the mysql query-optmizer is really weak in most cases (it appears 5.6 will be much better in many of them) such things i would always do with two queries in the application * first the sub-query * genearte the query above with the results in the app * fire up the final query Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Subquery taking too much time on 5.5.18?
Am 06.07.2012 17:46, schrieb Cabbar Duzayak: > Hi Everyone, > > I have been trying to understand why subqueries are taking tooo much > time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. > > In a nutshell, I have 2 tables: A and B. And, I do something like this: > > SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like 'X%'); > > Table A has 460,000 rows and Table B has 5000 rows, and A.id is my > primary key, and B.name is indexed. Also, the sub-query here (B.name > starts with X%) returns about 300 rows. query B can not used any key because 'like' never can use any key i try to avoid subqueries wherever i can becasue the mysql query-optmizer is really weak in most cases (it appears 5.6 will be much better in many of them) such things i would always do with two queries in the application * first the sub-query * genearte the query above with the results in the app * fire up the final query signature.asc Description: OpenPGP digital signature
Subquery taking too much time on 5.5.18?
Hi Everyone, I have been trying to understand why subqueries are taking tooo much time on my installation of MySQL 5.5.18 on Ubuntu 11.10 release. In a nutshell, I have 2 tables: A and B. And, I do something like this: SELECT * FROM A WHERE A.id IN (SELECT A_ID FROM B WHERE B.name like 'X%'); Table A has 460,000 rows and Table B has 5000 rows, and A.id is my primary key, and B.name is indexed. Also, the sub-query here (B.name starts with X%) returns about 300 rows. For some weird reason, this query takes a ton of time (I cancelled it after 750 seconds). I looked at the query plan with EXPLAIN and it could not find an index to use for table A and looks like it is doing a table scan (even though A.id is the primary key)… To understand it better, I divided it up, and sent two queries separately as follows:: "SELECT A_ID FROM B WHERE B.name like 'X%'" takes 0.002 second. For testing purposes, I concatenated all ids from this query and send a hard-coded query on A like: SELECT * FROM A WHERE A.id in (1,2,3,4,5…..) and this takes 0.002 second. Basically, both queries are super fast, but when I combine them via IN w/sub-query, the thing spends a lot more time? As an alternative, I tried using JOIN as follows: SELECT A.* FROM A INNER JOIN B ON (A.id = B.A_id) WHERE B.name like 'X%'; and this takes 0.04 seconds JOIN is also fast, but there are cases where I really need IN subqueries. I would really really appreciate it if you can shed some light on this issue and tell me what I am doing wrong and/or how I can fix this? Thanks a ton. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
