Considering a site with 100.000+ photos and growing. All photo tags are separated with comma.
Is better to put all photo tags into 1 row or separate?
photoId | tag
1 | sea
1 | beach
1 | night
33 | cars
SELECT photoId FROM photos WHERE tag='tagname'
photoId | tags
1 | sea, beach, night
2 | cars
SELECT photoId FROM photos WHERE tag IN ('tagname')
Which one is better, or is any other better way?
new tag each row is significantly better, see NORMALIZATION for clarity.
what guelphdad said
besides, you can't use WHERE tag IN ('tagname'), you have to use FIND_IN_SET, and of course this requires a table scan which gets slower and slower the more rows you have...