In this case, u require 2 indexes on table b.
1. WHERE b.basetype = 0 (requires index on b.basetype)
2. b.BoardID = m.BoardID (requires index on b.BoardID)
However, you are only allowed one index per table join.
Hence you need ONE composite index on table b with the fields b.basetype
and b.BoardID.
Do you have that?
Andrew Braithwaite wrote:
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]
