I tried this, but it is slow as well, but it went down to something
like 20 seconds.
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.
On Fri, Jul 6, 2012 at 7:37 PM, Stillman, Benjamin
> 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.reindl@stripped]
> Sent: Friday, July 06, 2012 11:58 AM
> To: mysql@stripped
> 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
>> 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