List:General Discussion« Previous MessageNext Message »
From:George Law Date:February 23 2006 12:06pm
Subject:Re: Query returns to many results
View as plain text  
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!
>
> -- 
> Kind Regards
> Schalk Neethling
> Web Developer.Designer.Programmer.President
> Volume4.Business.Solution.Developers
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
> 

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