Hi,
Does anybody know what is the best way to full text query
some database with a many to many table efficiently?
(This is for a web front end written in perl.)
Let me precise the question with a simple example:
TABLE Artist
id | Name | Surname | BirthDate
TABLE InBand
band_id | artist_id
FOREIGN band_id
FOREIGN artist_id
TABLE Band
id | Name
TABLE CD
id | title | year | band_id
FOREIGN band_id
Now a web user come and input:
find CDs with (artist.age=23 and title="hello world") or
(artist.name=smi.* and year=2003 or
artist.surname=b?ob)
Since users come with incredible queries (that is with a somehow
random number of args and structure), it seems to be impossible
to prepare SQL statements (caching)?
So the question is what is the best way to perform such queries efficiently?
Thanks in advance,
Olivier.
