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" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
