I have the database structure:
| Tag | | Article_Tag | | Article | --------------- --------------- ----------------- id tag_id article_id name article_id title type (numeric)
In a search field I'm entering various tag names and the search must return the Articles which contain those tag (or at least a part of them).
type field it's a numeric value representing the weight of the tag in it's article.
The query that I'm using now for solving this problem is:
SELECT DISTINCT sum(article_tag.type), article.id, article.title FROM tag JOIN article_tag ON tag.id = article_tag.tag_id JOIN article ON article.id = article_tag.article_id WHERE [tag.name LIKE ? OR] x n GROUP BY article.id ORDER BY sum(article_tag.type) DESC LIMIT ?
[tag.name LIKE ? OR] x n represents that tag.name LIKE ? OR will be repeated for the n tags entered in the search field. (For example the "sailing adventure ocean" string will be splitted and the three words will be compared with the tag.name).
The ordering by sum is required because we need some kind of "most relevant articles" feature.
The tag.name field is indexed and the DB Vendor is MySQL.
My concern is linked to the scalability of this structure. I don't think that having a large number of rows in the Tag table and lots of
OR clauses in that query will lead to a good response time. I ran some tests and the results were acceptable but I'm wondering if there is another solution for my problem. Maybe NoSql?