Jon Drukman wrote:
Andrew Braithwaite wrote:
Sorry, I meant to say is the 'BoardID' field indexed on the MBOARD table
too?
yes, BoardID is the primary key. BaseType is also indexed.
from the EXPLAIN output i can see that mysql is choosing to use BaseType
as the index for MBOARD (as we know, mysql can only use one index per
table.)
i guess that means it has to do the join without an index. that might
be why it's slow. i wonder if that can be worked around?
No. Here is your query rewritten with an explicit JOIN:
SELECT COUNT(1) FROM MSGS m
JOIN MBOARD b ON m.BoardID = b.BoardID
WHERE b.BaseType = 0;
The only condition which restricts which rows to consider is the requirement
b.BaseType = 0. Now look at the EXPLAIN output. MySQL is using the index on
BaseType, as it should. Apparently, 48614 rows have BaseType=0. For each row
found in MBOARD, MySQL is using the index on BoardID to find corresponding
rows in MSGS. The EXPLAIN output suggests there are about 277 rows in MSGS
for each row in MBOARD. That's a total of about 13,466,078 rows to count.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
