Hello. Please, provide the output of EXPLAIN for your queries. The performance should improved if you make e.CustID and c.CustID the same type (one of them is unsigned). Have you run ANALYZE TABLE on your tables?
Scott Baker wrote: > 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 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
