Hello,
I would like to ask some opinions on the best DB strategy for the
following problem:
I have a table containing information about artists (names, dates,
descriptions in three languages etc). The full-text index is put on 16
fields.
A user can select records to have access to (like favorite artists).
There are two possible full-text searches: through the whole table and
only within user's records.
The queries are like:
[1] SELECT some_fields, MATCH(index_fie lds) AGAINST (search_phrase) as
score FROM artists ORDER BY score LIMIT 1000
and
[2] SELECT some_fields, MATCH(index_fie lds) AGAINST (search_phrase) as
score FROM artists WHERE artist_id IN (users_artists_ ids) ORDER BY
score LIMIT 1000
Now I would like to have a versioning capability in my system - that
is every record may have different versions.
The main search (first query [1]) should be done only through the
versions marked as current.
User's favorite records may be ones of current version and some that
are outdated.
The user search (second query [2]) should go trough user's favorite
records.
Here are my questions (about the DB strategy):
1) Should I keep all records (current and outdated ones) in one table.
Doesn't it have influence one the indices and therefore the score
(MATCH AGAINST)?
2) If I keep current versions in one table and other ones in a second
table I assume the main search [1] will work as it works now and I
would have to modify the second query [2] to use the JOIN statement,
right?
What is the best solution?
Thank you for hints. I hope I'm clear enough.
best regards, cyprian
I would like to ask some opinions on the best DB strategy for the
following problem:
I have a table containing information about artists (names, dates,
descriptions in three languages etc). The full-text index is put on 16
fields.
A user can select records to have access to (like favorite artists).
There are two possible full-text searches: through the whole table and
only within user's records.
The queries are like:
[1] SELECT some_fields, MATCH(index_fie lds) AGAINST (search_phrase) as
score FROM artists ORDER BY score LIMIT 1000
and
[2] SELECT some_fields, MATCH(index_fie lds) AGAINST (search_phrase) as
score FROM artists WHERE artist_id IN (users_artists_ ids) ORDER BY
score LIMIT 1000
Now I would like to have a versioning capability in my system - that
is every record may have different versions.
The main search (first query [1]) should be done only through the
versions marked as current.
User's favorite records may be ones of current version and some that
are outdated.
The user search (second query [2]) should go trough user's favorite
records.
Here are my questions (about the DB strategy):
1) Should I keep all records (current and outdated ones) in one table.
Doesn't it have influence one the indices and therefore the score
(MATCH AGAINST)?
2) If I keep current versions in one table and other ones in a second
table I assume the main search [1] will work as it works now and I
would have to modify the second query [2] to use the JOIN statement,
right?
What is the best solution?
Thank you for hints. I hope I'm clear enough.
best regards, cyprian