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
Re: GA download reverted back to 5.5.24?
Charming, Noel. Are you Devops? :-D On Fri, Jul 6, 2012 at 3:09 AM, Noel Butler wrote: > ** > For those interested 5.5.25a has been released overnight, long after > oracle claimed it was there. > frankly., I think they ought to have use 5.5.26. > > To those who replied to me directly, a few facts... > > 1/ it never affected me directly - my gripe with them was on principle and > their actions (or lack thereof) towards those that were affected > > 2/ to the wanker who said people deserve what they get for untesting on > DEV bed first ... this is true _IF_ it was a major release. > (as I hope we all do) _BUT_ you don't expect to get fucked over by > a point release, to have that happen, shows incompetenceon the part of > the software developer, not the users. > > > > On Sat, 2012-06-30 at 14:15 +1000, Noel Butler wrote: > > I wonder if you would have the same opinion to say your Operating System > environment, Apache, php, any mainstream server daemon, how about they pull > the current version for a serious bug, but dont tell anyone... > > Oracle have been quick to announce new releases of mysql, but failed to > issue a notice saying " uhoh, you better not use it" instead, putting a > small notice, where, on a fricken manual page FFS. who the hell reads that! > and they say use version "a" which does not even exist, I'd hate to think > of how many high profile sites are at risk of being screwed over by yet > MORE oracle incompetence. > No one would think any less of them if they sent that notice, many would > be appreciative, but to "hide" such a serious issue that was enough for > them to withdraw and remove that version, is outright despicable. > > > > > > On Fri, 2012-06-29 at 22:58 -0400, Govinda wrote: > > >> That was nice of oracle to announce this wasn't it ...(/sarcasm) > >> > > I am not aligned with any side.. and I am also not known/qualified/respected > in this group enough to make much of a statement... but: > IMHO, In almost all matters, *appreciation* is the only approach that will > serve... let alone sustain happiness... > ...and especially when we consider what little we must give to have right to > use MySQL. > > Sure, desire for better communication/usability makes total sense.. but I am > just also observing/suggesting: please add (positively) to the atmosphere.. > for everyones' sake. Just us humans under the hood. > > -Govinda > > > >
Re: GA download reverted back to 5.5.24?
Am 06.07.2012 04:09, schrieb Noel Butler: > For those interested 5.5.25a has been released overnight, long after oracle > claimed it was there. > > 2/ to the wanker who said people deserve what they get for untesting on DEV > bed first ... > this is true _IF_ it was a major release. (as I hope we all do) _BUT_ you > don't > expect to get fucked over by a point release, to have that happen, > shows incompetence on the part of the software developer, not the users correct since this does not affect anybody and needs special conditions to be triggered it is simply IMPOSSIBLE to find it in 99.999% of all tests or why is not found with 'mysql-test-run.pl --parallel=1 --max-test-fail=0 --mysqld=--binlog-format=mixed --skip-ndb --force' which covers a much broader set of queries and conditions most applications can ever do? signature.asc Description: OpenPGP digital signature
