MySQL table intersections

[elmo88]

Hi all,

I am trying to figure out how to simulate an intersection in MySQL 4.1. I'm going to make a toy example to illustrate the problem.

I have 3 tables,
WordTable

| word id | word |
| 1 | rabbit |
| 2 | venus |
| ...
| 2600000 | apple |

SentenceTable
| sentence id | data |
| 1 | some data |
| 2 | some data |
| ....
| 2600000 | some data |


Mapping Table (SentenceWordTable)
Basically like an index
| Word Id | Sentence Id |
| 1 | 34 |
| 2 | 343 |
| 1 | 343 |
| 2 | 394 |
...
| 448 | 48535 |


What happens is I might want to search for sentences that are mapped to word ids (1, 4, 304, 399). How do I do it without writing a sql statement with 3 inner joins? The problem is if I go to 15 words, it would become a huge statement and it will run very slowly.

E.g. something like this might run very slowly + ugly
SELECT Distinct a.SentenceId
FROM SentenceWordTable a
INNER JOIN SentenceWordTable b
INNER JOIN SentenceWordTable c
...
INNER JOIN SentenceWordTable z
WHERE a.SentenceId = b.SentenceId ... AND y.SentenceId=z.SentenceId
AND a.WordId = 3 AND b.WordId = 5 ... AND z.WordId = 49

I hope to do something like

SELECT Distinct a.SentenceId FROM SentenceWordTable a WHERE a.WordId = 3
INTERSECT
SELECT Distinct b.SentenceId FROM SentenceWordTable b WHERE b.WordId = 53
...

This is for some biological data, but I'm using the word/sentence problem as an analogy.


Thanks!

[Banfa]

It appears to me that once SentenceWordTable is set up this is the only you are searching which should be quite fast.

You are using a notation that I am not quite familiar with I would have expected to see

SELECT Distinct SentenceId FROM SentenceWordTable WHERE WordId = 3
INTERSECT
SELECT Distinct SentenceId FROM SentenceWordTable WHERE WordId = 53

notice lack of a's and b's.

I take it what you want is to select a sentence that has all of a given number of words. Since MySql doesn't do INTERSECTions I think I would probably use a simple search to get all the data that I required and then post process that data in whatever language you are using.

SELECT Distinct SentenceId,WordId FROM SentenceWordTable WHERE WordId = 3 OR WordId = 53

Alternitively use a better database.