Ooooops, forgot to reply to this list Gleb:
+-------+-------+---------------+---------+---------+----------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+---------+---------+----------+------+-------------+ | c | index | PRIMARY | PRIMARY | 3 | [NULL] | 9371 | Using index | | e | ref | CustID | CustID | 3 | c.CustID | 1 | | +-------+-------+---------------+---------+---------+----------+------+-------------+ Hopefully that won't come across too funky for you. Anyway I ended up changing the CustID field in the EmailAddr table to NOT be unsigned (so both the field types are the same) and it came back immediately. So it appears from my preliminary tests that the problem was that the field types didn't match. What a weird problem, I never would have thought of that. Good catch! Scott Gleb Paharenko wrote: > 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 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
