Hi.
You may want to try a combined index (RecordNumber, WordNumber) on
wordindex, because the indexes you have can only restrict on field
(RecordNumber in the EXPLAIN below) e.g.
ALTER TABLE wordindex ADD UNIQUE(RecordNumber, WordNumber);
I presume that the RecordNumber/WordNumber pairs are unique (seems so
from what your tables look like). If not, you have to replace UNQIUE
by KEY.
Bye,
Benjamin.
On Tue, Feb 06, 2001 at 01:49:41PM +0100, [EMAIL PROTECTED] wrote:
[...]
> I did the EXPLAIN as you suggested.
>
> Here is the output:
>
> mysql> EXPLAIN SELECT B.* FROM maintable AS B ,
> -> wordindex AS YL1 ,
> -> wordindex AS YL2 ,
> -> wordindex AS YL3 WHERE
> -> YL1.WordNumber = 123 AND
> -> YL1.RecordNumber = B.RecordNumber AND
> -> YL2.WordNumber = 345 AND
> -> YL2.RecordNumber = B.RecordNumber AND
> -> YL3.WordNumber = 678 AND
> -> YL3.RecordNumber = B.RecordNumber AND
> -> B.Price >= 1000
> -> LIMIT 0,51;
> +-----+------+-----------------------+------------+-------+----------------+
> ----+----------+
> |table|type |possible_keys |key |key_len|ref
> |rows|Extra |
> +-----+------+-----------------------+------------+-------+----------------+
> ----+----------+
> |YL3 |ref |WordNumber,RecordNumber|WordNumber | 4 |???
> | 1 | |
> |B |eq_ref|PRIMARY |PRIMARY | 4
> |YL3.RecordNumber| 1 |where used|
> |YL2 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber
> | 23 |where used|
> |YL1 |ref |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber
> | 23 |where used|
> +-----+------+-----------------------+------------+-------+----------------+
> ----+----------+
> 4 rows in set (0.01 sec)
>
[...]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
