It looks like I spoke too soon. That field fix sped up the straight
join, but not the more complex query I had, it's still 0.3 seconds.
SELECT EmailID, EmailUsername, d.DomainName, e.CustID
FROM EmailAddr e, Domain d, CustInfo c
WHERE e.CustID = c.CustID AND e.DomainID = d.DomainID AND
(EmailUsername LIKE '%thompson%')
LIMIT 10;
This explains out like this
Slow (0.3 seconds)
+-------+--------+-----------------+---------+---------+------------+------+-------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-------+--------+-----------------+---------+---------+------------+------+-------------+
| c | index | PRIMARY | PRIMARY | 3 | [NULL]
| 9371 | Using index |
| e | ref | DomainID,CustID | CustID | 3 | c.CustID
| 1 | Using where |
| d | eq_ref | PRIMARY | PRIMARY | 2 | e.DomainID
| 1 | |
+-------+--------+-----------------+---------+---------+------------+------+-------------+
However if I force the index on EmailAddr:
SELECT EmailID, EmailUsername, d.DomainName, e.CustID
FROM EmailAddr e USE INDEX(Primary), Domain d, CustInfo c
WHERE e.CustID = c.CustID AND e.DomainID = d.DomainID AND
(EmailUsername LIKE '%thompson%')
LIMIT 10;
It's fast (0.02 seconds). So it's like Mysql isn't putting the
tables in the right order. Does it have to do with the order I put
the fields/tables in the query? The best way would be for mysql to
do a full table scan for anything that matches that LIKE query...
which should return 8 records, and then join on the CustID. As
opposed to doing the join first and joining 10,000 records, and then
grepping all those.
Fast (0.02 seconds)
+-------+--------+---------------+---------+---------+------------+-------+-------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+-------+--------+---------------+---------+---------+------------+-------+-------------+
| e | ALL | [NULL] | [NULL] | [NULL] | [NULL] |
12413 | Using where |
| d | eq_ref | PRIMARY | PRIMARY | 2 | e.DomainID |
1 | |
| c | eq_ref | PRIMARY | PRIMARY | 3 | e.CustID |
1 | Using index |
+-------+--------+---------------+---------+---------+------------+-------+-------------+
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]
