0

I'm building a search function in php for a database with 1k to 4k articles. It's going to be built on a tag system. I've been reading some tutorials, blog posts, q&a etc about it.

Most important for me is performance, I want it to be fast and light. In my special case I'm only going to have a set of few fixed tags. Example: Color:blue, Size:large etc. So if user filters/search "Large Blue" only the articles with these tags should show. There's going to be "Ajaxed" queries, so a light and fast query is important.

I can think of three ways of doing this:

Option 1:
Articles table
- id
- name
- tags (with a data string of tags comma separated)
- etc

Option 2.
Articles table
- id
- name
- etc

Tags table
- id
- name
- article ids (with a data string of article ids comma separated)

Option 3.
Articles table
- id
- name
- etc

Relational Tag Arts table
- Fkey tags id
- Fkey article id

Tags table
- id
- name
- etc

Which one of these would be fastest / best performance. I know it's not good practice to use comma separated data strings in a table field. But for me, since I just have a few set of tags, option 1 or 2 looks simple and faster than option 3?

Any other suggestions is of course welcome! Thanks

1
  • Hi. Did you add any primary key, auto increment, or index for your "Relational Tag Arts table"?
    – Ian Y.
    Nov 4, 2018 at 5:38

1 Answer 1

2

In my opinion option 3 is the best.

Option 1 has the problem that you always have to use a like on the tags field to find the articles.

Option 2 will cause a rewrite of the tag each time that an article is added or deleted. When there are a lot of articles for the tag then the record becomes long.

Both option 1 and 2 will end up with 'big' rows which means less rows in a physical block which means more physical reads and 'slower' result.

Option 3 has a simple setup. It is the way to present an M-to-N relation. Put an index on the name of the tag to help to speed up the finding of the tags (will not help much if you have a very limited number of tags). Adding an article or adding a tag to an article will be fast too.

6
  • Thanks for the answer! What's the downside with using a "like" on the tags field? It so few tags, so don't you think it would be faster than option 3? Since the query never has to read the article id data string, the big row won't be a problem in option 2? I see option 1 has this problem though...
    – galengodis
    Sep 15, 2015 at 9:07
  • Normally if you use like then the index is not used. About option 2. Suppose you have a tag that is 'used' for 2k rows and you add another article (or remove one) the whole tag would have to be read and written back. Will it still fit in a single/same block? Option 3 is the most relational solution. Search the internet for database normalization.
    – Marco
    Sep 15, 2015 at 9:27
  • Ah ok. So "like" doesn't work that good with an index. Yes the tag has to be read and written back, but I don't see that as a problem since new articles doesn't get added that often. And when they do its just two queries. I know the benefits with normalization, in case I had loads of tags / keywords, I understand it would be the correct way of go in regards of speed. But since I only have a few set of tags I can't see option 3 being faster than option 2?
    – galengodis
    Sep 15, 2015 at 9:53
  • It is not all black and white :-) If there are not many changes in the articles nor tags then option 1 will work too. Since there are not that many articles (2k to 4k) and very little tags the difference will not be huge. The user will not notice it. Option 3 is more flexible in case the needs change or the number of articles and/or tags grow. Do you plan statistics on the tags? Option 3 is easier for that purpose.
    – Marco
    Sep 15, 2015 at 10:20
  • Ok, so even if the third option has loads more of rows it will be the same in speed performance as option 2? The statistics point is a good point! Although i could handle statistics in a separate table or outside the database.
    – galengodis
    Sep 15, 2015 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.