Sorry, I meant to say is the 'BoardID' field indexed on the MBOARD table too?
Cheers, A On 16/7/05 00:01, "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote: > Hi, > > You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the > BoardID field indexed on the MSGS table too? If not then that may be your > problem. > > Cheers, > > Andrew > > > On 15/7/05 23:31, "Jon Drukman" <[EMAIL PROTECTED]> wrote: > >> i'm trying to run this query: >> >> SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = 0 AND m.BoardID >> = b.BoardID; >> >> MSGS has 9.5 million rows, and is indexed on BoardID >> MBOARD has 69K rows and is indexed on BaseType >> >> EXPLAIN shows: >> >> mysql> explain SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = >> 0 AND m.BoardID = b.BoardID; >> +-------+------+------------------+------------+---------+-----------+------->> + >> -------------+ >> | table | type | possible_keys | key | key_len | ref | >> rows | Extra | >> +-------+------+------------------+------------+---------+-----------+------->> + >> -------------+ >> | b | ref | PRIMARY,BaseType | BaseType | 1 | const | >> 48614 | | >> | m | ref | BoardIndex | BoardIndex | 4 | b.BoardID | >> 277 | Using index | >> +-------+------+------------------+------------+---------+-----------+------->> + >> -------------+ >> >> the query takes several minutes to run. shouldn't this be a simple case >> of doing some math on index values? >> >> -jsd- >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
