Hello mysql,
Why sum() influences use of indexes for the table ac (tblAccountActivity)
mysql> EXPLAIN
-> SELECT
-> c.CustomerID, c.CompanyName,
-> c.ContactFirstName, c.ContactLastName, c.BillAddress1,
-> c.MobilPhone, c.WorkPhone, c.HomePhone,
-> sum(ac.Credit), sum(ac.Debit)
-> FROM tblCustomer c
-> INNER JOIN tblUser u ON u.CustomerID=c.CustomerID
-> INNER JOIN tblAccountActivity ac ON u.UserID=ac.UserID
-> WHERE c.Dealer=4
-> GROUP BY c.CustomerID;
+-------+--------+--------------------+---------+---------+--------------+--------+-----------------+
| table | type | possible_keys | key | key_len | ref | rows |
|Extra |
+-------+--------+--------------------+---------+---------+--------------+--------+-----------------+
| ac | ALL | UserID | NULL | NULL | NULL | 141800 |
|Using temporary |
| u | eq_ref | PRIMARY,CustomerID | PRIMARY | 4 | ac.UserID | 1 |
| |
| c | eq_ref | PRIMARY,K1,IDX2 | PRIMARY | 4 | u.CustomerID | 1 |
|where used |
+-------+--------+--------------------+---------+---------+--------------+--------+-----------------+
mysql> EXPLAIN
-> SELECT
-> c.CustomerID, c.CompanyName,
-> c.ContactFirstName, c.ContactLastName, c.BillAddress1,
-> c.MobilPhone, c.WorkPhone, c.HomePhone
-> FROM tblCustomer c, tblUser u, tblAccountActivity ac USE INDEX (UserID)
-> WHERE c.Dealer=4 AND u.CustomerID=c.CustomerID AND u.UserID=ac.UserID
-> GROUP BY c.CustomerID;
+-------+------+--------------------+------------+---------+--------------+------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
|Extra |
+-------+------+--------------------+------------+---------+--------------+------+-----------------------------+
| c | ref | PRIMARY,K1,IDX2 | IDX2 | 2 | const | 407 |
|where used; Using temporary |
| u | ref | PRIMARY,CustomerID | CustomerID | 4 | c.CustomerID | 18 |
| |
| ac | ref | UserID | UserID | 4 | u.UserID | 47 |
|Using index |
+-------+------+--------------------+------------+---------+--------------+------+-----------------------------+
Best regards,
Andrew Sitnikov
e-mail : [EMAIL PROTECTED]
GSM: (+372) 56491109
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
