It is going to do a full table scan of the Monday table trying to return all rows that are in the Friday table. You have restriction criteria outside of your join.
-----Original Message----- From: Daniel Cummings To: 'Victor Pendleton'; [EMAIL PROTECTED] Sent: 5/25/04 2:05 PM Subject: RE: Understanding Explain in 4.1 Victor- It's a little misleading. It looks like it's going to do a full table scan of the Monday table. Dan -----Original Message----- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 6:18 AM To: 'Daniel Cummings '; '[EMAIL PROTECTED] ' Subject: RE: Understanding Explain in 4.1 It does not have to use an index because you have not supplied row restriction criteria. An index is being used to relate the two tables. -----Original Message----- From: Daniel Cummings To: [EMAIL PROTECTED] Sent: 5/24/04 6:05 PM Subject: Understanding Explain in 4.1 These are two simple stock quotes tables that contain a symbol and a close amount. Explain Select Monday.Symbol, Friday.Close, Monday.Close From Monday Force Index(Symbol) Inner Join Friday Force Index(Symbol) ON Monday.Symbol = Friday.Symbol Symbol is a VarChar(20) in both tables. There is an index on Symbol for both of these table. The resultant Explain shows that +----------+------+-----------------+------------+---------+------------ ---- ---+-----------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------+------+-----------------+------------+---------+------------ ---- ---+-----------+-------------+ | Monday | All | Symbol | [Null] | [Null] | [Null] | 60134 | | +----------+------+-----------------+------------+---------+------------ ---- ---+-----------+-------------+ | Friday | ref | Symbol | Symbol | 21 | Monday.Symbol | 1 | where used | +----------+------+-----------------+------------+---------+------------ ---- ---+-----------+-------------+ Why aren't both using the Symbol key? Why isn't the Force Index working? TIA Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
