Hi,
In previous e-mail I asked how to improve a word search and used some of
the suggestions. Now I'm doing a 2 word search and need a little advice.
Background:
There are 2 tables: word and content.
content consists of 2 fields:
rec_id int(11) unsigned not null,
description varchar(200) not null
word consists of 2 fields:
word int(11) unsigned not null,
rec_id int(11) unsigned not null
word is created from content. Each actual word from the field
content.description is created as a record in the table word with its rec_id.
The field word.word is the CRC of the actual word hence the integer type.
I've setup a query as such for the words: new york
There are ~70,000 records with the word new, and ~10,000 with the word new.
Query is:
select description from content, word w1, word w2 where w1.word=1810056261
and w1.rec_id=content.rec_id and w2.word=3012632499 and
w2.rec_id=content.rec_id ORDER BY w1.word, w2.word;
When I do an explain it shows that its checking over ~80,000 records
+---------+--------+------------------------+-------------+---------+-------
----+-------+-------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+---------+--------+------------------------+-------------+---------+-------
----+-------+-------------+
| w2 | ref | word_index1 | word_index1 | 4 |
const | 10982 | where used |
| content | eq_ref | PRIMARY,content_index1 | PRIMARY | 4 |
w2.rec_id | 1 | Using index |
| w1 | ref | word_index1 | word_index1 | 4 |
const | 84725 | where used |
+---------+--------+------------------------+-------------+---------+-------
----+-------+-------------+
My question: Is it possible to setup the query to tell mysql to search the
smallest subset only when you have a conditional AND in this kind of join.
Thanks in advance.
regards
David
---------------------------------------------------------------------
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
