Hi, I found the solution for this issue. I separate into 2 queries and put the result onto an array, and do PHP array_intersect_assoc to match. Much more simpler, fast, about 200 thousands record matched in 10 seconds. Many thanks for your helps.
Regards, Willy On Thu, 2008-05-22 at 07:40 -0700, Rob Wultsch wrote: > Sub queries suck in mysql. It is probably worth while to post the results of: > EXPLAIN SELECT msgdata > FROM sent_sms > WHERE momt = 'MT' > AND binfo IN (SELECT > binfo > FROM sent_sms > WHERE momt = 'DLR') > > Are binfo and momt indexed? If not, they probably should be. A > replacement for the sub query should be something like: > SELECT s1.msgdata > FROM sent_sms AS s1 > INNER JOIN sent_sms AS s1 ON s1.binfo =s2.binfo > WHERE s1.momt = 'MT' > > > > On Thu, May 22, 2008 at 2:05 AM, sangprabv <[EMAIL PROTECTED]> wrote: > > Hi, > > Thanks for the reply. The query seems doing something but after minutes > > of waiting it still return no result. > > > > Regards, > > > > > > Willy > > On Wed, 2008-05-21 at 23:06 -0400, David Lazo wrote: > >> Would this work for you? > >> > >> SELECT msgdata > >> FROM sent_sms > >> WHERE momt = 'MT' > >> AND binfo IN (SELECT > >> binfo > >> FROM sent_sms > >> WHERE momt = 'DLR') > >> > >> > >> David > >> > >> > >> On 5/21/08 10:30 PM, "sangprabv" <[EMAIL PROTECTED]> wrote: > >> > >> > Hi, > >> > I tried to look for records from a table with this query: > >> > SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT > >> > binfo FROM sent_sms WHERE momt = 'DLR' ) > >> > But MySQL returns this error: > >> > #1242 - Subquery returns more than 1 row > >> > I tried also with ANY, IN, EXISTS. > >> > And modified the query into: > >> > SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') > >> > AS t1 > >> > WHERE momt = 'MT'. But none works. > >> > What I want to view is, all records which has momt = 'MT' and binfo from > >> > the same table where has momt = 'DLR' and has the same binfo. TIA > >> > > >> > Regards, > >> > > >> > > >> > Willy > >> > > >> > >> > > -- > > [sangprabv] > > http://www.sangprabv.web.id > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- [sangprabv] http://www.sangprabv.web.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
