List:General Discussion« Previous MessageNext Message »
From:Schalk Date:February 23 2006 1:55pm
Subject:Re: Query returns to many results
View as plain text  
George Law wrote:
> Schalk ,
>
> You need to specify the unifying column between your ablb and abm tables.
>
> ie - in your where, "and ablb.id=abm.id"
>
> Once you get this so it returns expected results, you can run the 
> query, prefaced with
> "explain" and it will give you an idea on the way mysql is running the 
> query.  This has helped me determine
> some additional indexes that greatly speed up my queries.
>
> -- 
> George
>
>
> ----- Original Message ----- From: "Schalk" <schalk@stripped>
> To: <mysql@stripped>
> Sent: Thursday, February 23, 2006 6:59 AM
> Subject: Query returns to many results
>
>
>> Greetings All,
>>
>> Please have a look at the following query:
>>
>> SELECT abm.mem_number, abm.first_name, abm.last_name, 
>> abm.area_represented, abm.age, abm.sex, abm.cup,
>> ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + 
>> ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc 
>> + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc 
>> + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp 
>> + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + 
>> ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + 
>> ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board 
>> ablb, ab_members abm
>> WHERE abm.sex = 'Female' AND abm.cup = 'kids'
>> ORDER BY total_points DESC
>>
>> Now this query is run over two tables and the ab_members table 
>> contains around 302 rows. Around 1/3 of these will be where cup=kids. 
>> However, when this query is run it returns 20,700 results :0 Any idea 
>> why this is? Also, any help or pointers as to how I can optimize this 
>> query will be much appreciated. Thank you!
>
Thanks George! It works perfectly. Now to optimize this bugger.

-- 
Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Business.Solution.Developers


Thread
Query returns to many resultsSchalk23 Feb
  • Re: Query returns to many resultsGeorge Law23 Feb
    • Re: Query returns to many resultsSchalk23 Feb
      • Re: Query returns to many resultsSGreen23 Feb
        • Re: Query returns to many resultsMartijn Tonies23 Feb
  • Re: Query returns to many resultsPeter Brawley24 Feb
    • Re: Query returns to many resultsSchalk24 Feb