| pedro mpa 2006-01-31, 11:23 am |
| > -----Mensagem original-----
> De: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]
>=20
> "pedro mpa" <mail.pmpa@sapo.pt> wrote on 01/31/2006 12:49:48 AM:
>=20
I[color=darkred]
> try
>=20
> You don't need a subquery to get these results. You can also use a =
regular
> low-tech JOIN:
>=20
> CREATE TEMPORARY TABLE tmpDateTotals SELECT
> receipts.date
> , sum(receipt_itens.price) total_price
> FROM receipts
> LEFT JOIN receipt_itens
> ON receipts.id =3D receipt_itens.id_receipt
> HAVING total_price >=3D ".$minprice."
> AND total_price <=3D ".$maxprice."
> GROUP BY receipts.date;
>=20
> SELECT receipts.*, dt.total_price
> FROM receipts
> INNER JOIN tmpDateTotals dt
> ON dt.date =3D receipts.date
> ORDER BY receipts.date desc;
>=20
> DROP TEMPORARY TABLE tmpDateTotals;
>=20
> The problem with your original query was that you were trying to =
compare
> the results of a calculation in your WHERE clause. Results do not =
exist
> when WHERE clauses are evaluated but they do by the time the HAVING
> clauses are checked. The column`total_price` only exists _after_ the =
SUM()
> function is computed which happens _after_ the restrictions of any ON =
and
> WHERE clauses are applied to your source data. The HAVING clause is
> specifically designed to handle this type of comparison. I will bet
> dollars to doughnuts that your subquery version of this query will =
execute
> 5 to 10 times slower than my version without the subquery. Can you =
please
> try both and report your results?
Thank you for your good explanation.
The query I wrote is an example of a more complex query I am building in
which I use a lot of subquerys in detriment of JOIN clause. When I =
finish
rebuilding the query I will compare performance and post it.
I didn't know that subquerys are much slower than JOINS.
Thank you.
Pedro.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|