Mysql Query Optimization?

[Scott Baker]

I have a query that's taking about 0.3 seconds to run and it should
be faster I think. I've read all about query optimization and
forcing indexes and I'm not entirely sure what I'm having this
problem. Google finds all kinds of basic information like using
indexes, and not doing math on the query fields. Stupid stuff I'm
not doing.

I have two tables EmailAddr and CustInfo. That look like this:

+------------------+-----------------------+------+-----+---------+
| Field            | Type                  | Null | Key | Default |
+------------------+-----------------------+------+-----+---------+
| EmailID          | mediumint(8) unsigned |      | PRI | NULL    |
| EmailUsername    | varchar(25)           | YES  | MUL | NULL    |

| EmailPassword    | varchar(50)           | YES  |     | NULL    |
| CustID           | mediumint(8) unsigned |      | MUL | 0       |

+------------------+-----------------------+------+-----+---------+

And

+-----------------+-----------------------+------+-----+-----------+
| Field           | Type                  | Null | Key | Default   |
+-----------------+-----------------------+------+-----+-----------+
| CustID          | mediumint(8)          |      | PRI | 0         |
| CustAddress     | varchar(150)          | YES  |     | NULL      |
| CustCity        | varchar(50)           | YES  |     | NULL      |
| CustState       | varchar(50)           | YES  |     | NULL      |
| etc...                                                           |
+-----------------+-----------------------+------+-----+-----------+

Where I select all the records from either table it's 0.02 seconds.
There are about 10,000 records in each table. If I try and join the
Email table and CustInfo table on CustID (which is in indexed field
in both tables) the query time jumps to 0.3 seconds.

SELECT EmailID, e.CustID
FROM EmailAddr e, CustInfo c
WHERE e.CustID = c.CustID;

If I tell it to use the index primary it's fast (0.02 seconds)

SELECT EmailID, e.CustID
FROM EmailAddr e USE INDEX (Primary), CustInfo c
WHERE e.CustID = c.CustID;

#1) Why can't it find that relationship itself...
#2) Why does telling it to use the Primary key for EmailAddr help at
all!?! It's not even a query/join field.

Scott

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]