Bug #7009 MySQL Slow
Submitted: 4 Dec 2004 16:56 Modified: 12 Feb 2005 13:44
Reporter: Mathieu Tremblay Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1.7 OS:Windows (windows XP)
Assigned to: Assigned Account CPU Architecture:Any

[4 Dec 2004 16:56] Mathieu Tremblay
Description:
I want to know why my query is so slow ??

MyQuery
SELECT Total-(SELECT IFNULL(SUM(Amount),0.00) FROM Payment WHERE RefInvoice=Invoice.Number) AS AmountToPay
FROM Invoice
WHERE Number=2540

What explain said !!

id=1
select_type=PRIMARY
table=Invoice
type=const
possible_key=PRIMARY
key=PRIMARY
key_len=4
ref=const
rows=1
Extra=

id=2
select_type=DEPENDENT SUBQUERY
table=Payment
type=ref
possible_key=IX_InvoicePayment,IX_Payment_RefInvoice
key=IX_InvoicePayment
key_len=4
ref=const
rows=10
Extra=

Why its take over 360kb of traffic ??? all COUNT and SUM are really slow

Thank

Mathieu

How to repeat:
Create the table and do the query .
[5 Dec 2004 11:39] Hartmut Holzgraefe
Subqueries are not highly optimized yet, see
http://dev.mysql.com/doc/mysql/en/Optimizing_subqueries.html

Regarding the amount of data transfered i'd like to ask you for a 
table dump to further analyze this issue.
[12 Feb 2005 13:44] Heikki Tuuri
No feedback in 2 months.