Thanks for the suggestion, but according to explain we are in worse shape than before. In both cases the multi-column index is ignored. I am going to try fiddling with the index col order to see if this helps.
Here is what it comes up with as you suggested: Query1: explain SELECT COUNT(*) as NumRecords FROM spam t1 INNER JOIN recip t2 ON t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t3.MailInID WHERE t1.RecipID > 34035098 AND t1.Status="present"; +-------+--------+---------------+---------+---------+------------+--------- +--------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+------------+--------- +--------------------------+ | t1 | range | RecipID | RecipID | 8 | NULL | 488474 | Using where | | t2 | eq_ref | PRIMARY | PRIMARY | 8 | t1.RecipID | 1 | Using index | | t3 | index | NULL | PRIMARY | 8 | NULL | 5572118 | Using where; Using index | +-------+--------+---------------+---------+---------+------------+--------- +--------------------------+ Not sure if the t1 result is better or worse but the t3 line is bad news. Query2: explain SELECT t1.*, t3.* FROM spam t1 INNER JOIN recip t2 ON t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t2.MailInID = t3.MailInID WHERE t1.Status="present" AND t1.RecipID > 34035098; +-------+--------+-------------------------+---------+---------+------------ -+--------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+-------------------------+---------+---------+------------ -+--------+-------------+ | t1 | range | RecipID | RecipID | 8 | NULL | 402604 | Using where | | t2 | eq_ref | PRIMARY,MailInID | PRIMARY | 8 | t1.RecipID | 1 | | | t3 | eq_ref | PRIMARY,MID_AID,MID_DID | PRIMARY | 8 | t2.MailInID | 1 | | +-------+--------+-------------------------+---------+---------+------------ -+--------+-------------+ Sort of about the same. > Did you try: > SELECT COUNT(*) as NumRecords FROM spam t1 INNER JOIN recip t2 0N > t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t3.MailInID WHERE > t1.RecipID > 34035098 AND t1.Status="present"; > > re-written as: > SELECT t1.*, t3.* FROM spam t1 INNER JOIN recip t2 ON t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t2.MailInID = t3.MailInID WHERE t1.Status="present" AND t1.RecipID > 34035098; (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
