|
Home > Archive > MySQL ODBC Connector > February 2006 > Query returns to many results
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Query returns to many results
|
|
| Schalk 2006-02-25, 9:43 am |
| 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...sie.nctu.edu.tw
| |
| George Law 2006-02-25, 9:43 am |
| 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@volume4.com>
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!
>
> --
> 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...her
e.net
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Schalk 2006-02-25, 9:43 am |
| 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@volume4.com>
> To: <mysql@lists.mysql.com>
> Sent: Thursday, February 23, 2006 6:59 AM
> Subject: Query returns to many results
>
>
>
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/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2006-02-25, 9:43 am |
| --=_alternative 005326B98525711E_=
Content-Type: text/plain; charset="US-ASCII"
Schalk <schalk@volume4.com> wrote on 02/23/2006 08:55:01 AM:
> George Law wrote:
tables.[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
> Thanks George! It works perfectly. Now to optimize this bugger.
>
> --
> Kind Regards
> Schalk Neethling
> Web Developer.Designer.Programmer.President
> Volume4.Business.Solution.Developers
>
Schalk,
You wouldn't have even run into this as an issue if you had used the
explicit JOIN form. Again, I blame the documentation for only
demonstrating the "lazy" form of INNER JOIN declaration almost
exclusively. I believe that by only demonstrating the comma-separated
join, they have created the impression that it is a "preferred" method. I
strongly discourage the use of that form of declaring table joins for the
very reason you posted. If you had used the explicit form:
SELECT
....(all of your columns)...
FROM ab_leader_board ablb
INNER JOIN ab_members abm
ON ablb.id=abm.id (or whatever is appropriate)
WHERE ...
It should have be intuitively obvious that you had left out the ON clause
from your original query. As it was, your missing JOIN conditions were
just not noticed because of all of the other activity in your whole
statement. This is a very frequent problem with the join syntax you used
in your original query.
Again, I implore all SQL coders to use the explicit JOIN syntax on all
platforms that support it (Oracle being a well-known exception). It makes
it much easier to catch logical errors just like Schalk ran into in his
original post. The explicit form is also the only way to declare outer
joins in MySQL so you will have to use it sooner or later. Please, again,
I ask the documentation team to modify the SQL examples in the manual
(especially in the tutorial section) to use the explicit JOIN forms.
Humbly yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 005326B98525711E_=--
| |
|
|
| Peter Brawley 2006-02-25, 9:43 am |
| --=======AVGMAIL-43FE9C0C0880=======
Content-Type: multipart/alternative; boundary=------------ 04000100010502020909
0404
-------------- 04000100010502020909
0404
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
/>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 /
That's because your ...
FROM ab_leader_board ablb, ab_members abm
calls for a cross join--it asks for every logically possible combination
of ablb and abm rows. From the rest of your query, it appears you need
something like ...
FROM ab_leader_board ablb
INNER JOIN ab_members abm USING (< name_of_joining_colu
mn> )
Also, do you really mean to sum all those ablb column values after
having already called for all ablb column values with ablb.* ?
PB
-----
Schalk wrote:
> 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!
>
-------------- 04000100010502020909
0404
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<i>>Now this query is run over two tables and the ab_members table
contains <br>
>around 302 rows. Around 1/3 of these will be where cup=kids.
However, <br>
>when this query is run it returns 20,700 results </i><br>
<br>
That's because your ...<br>
<br>
<tt>FROM ab_leader_board ablb, ab_members abm
</tt><br>
<br>
calls for a cross join--it asks for every logically possible
combination of ablb and abm rows. From the rest of your query, it
appears you need something like ...<br>
<br>
<tt>FROM ab_leader_board ablb<br>
INNER JOIN ab_members abm USING (& lt;name_of_joining_c
olumn>)</tt><br>
<br>
Also, do you really mean to sum all those ablb column values after
having already called for all ablb column values with ablb.* ? <br>
<br>
PB<br>
<br>
-----<br>
<br>
Schalk wrote:
<blockquote cite="mid43FDA38F.30608@volume4.com" type="cite">Greetings
All,
<br>
<br>
Please have a look at the following query:
<br>
<br>
SELECT abm.mem_number, abm.first_name, abm.last_name,
abm.area_represented, abm.age, abm.sex, abm.cup,
<br>
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
<br>
WHERE abm.sex = 'Female' AND abm.cup = 'kids'
<br>
ORDER BY total_points DESC
<br>
<br>
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!
<br>
<br>
</blockquote>
</body>
</html>
-------------- 04000100010502020909
0404--
--=======AVGMAIL-43FE9C0C0880=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.0.0/267 - Release Date: 2/22/2006
--=======AVGMAIL-43FE9C0C0880=======
Content-Type: text/plain; charset=us-ascii
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-43FE9C0C0880=======--
| |
| Schalk 2006-02-25, 9:43 am |
| Peter Brawley wrote:[color=darkred
]
> />Now this query is run over two tables and the ab_members table contains
>
> That's because your ...
>
> FROM ab_leader_board ablb, ab_members abm
>
> calls for a cross join--it asks for every logically possible
> combination of ablb and abm rows. From the rest of your query, it
> appears you need something like ...
>
> FROM ab_leader_board ablb
> INNER JOIN ab_members abm USING (< name_of_joining_colu
mn> )
>
> Also, do you really mean to sum all those ablb column values after
> having already called for all ablb column values with ablb.* ?
>
> PB
>
> -----
>
> Schalk wrote:
Greetings Peter,
Well, with regards to the ablb.*, I need access to each individual column as well as to the sum of all of those columns, so I think I need to do both, or don't I?
--
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...sie.nctu.edu.tw
|
|
|
|
|