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 <bstill...@limitedbrands.com> 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
