0

I just discovered the SET column type for MySQL tables. I have a photos table with ARIA engine where I want to store tags/keywords for each photo.

Now I use a VARCHAR type and then find the photos with a MATCH query and a FULLTEXT INDEX, the tags are space separated like "one two three".

When I use SET, I could possibly use tags with spaces, like "a tag with spaces, another one", but how fast would this be? And can I use FIND IN SET to list photos by tag and also list all tags from a range of photos?

Which is the better choice for tags, SET, VARCHAR or even another type?

Improve this question

1 Answer 1

Reset to default
1

Neither

Add another table for tags with one row per tag and photo

  • either just (photo_id, tag) - tag is varchar (or maybe enum, but then it is harder to add more tags), so if two photos have the same tag, there is some redundancy
  • or two tables - (tag_id, tag) and (photo_id, tag_id) where each tag is unique, so no duplication of texts, and the second table just links tags to photos, thats more effective but a bit more complex

Do not store multiple values in one column if you want to work with them (search by them etc.) - a database is not just a CSV storage and this is what rows and joins are for. It's called Normalization - learn about it and it will save you from many headaches.

Improve this answer
7
  • I had the two tables solution, but it became slow (12000 rows in pic_tag table) and was a bit more difficult to update, edit , delete tags. now with fulltext index it seems much faster and easier to show all photos for 'tag', 'Tag', 'taG' and so on. that is why I changed this yesterday to try if it would be better.
    – haheute
    Nov 11, 2015 at 20:27
  • Did you have proper indexes on those tables? And was the tag column defined with a case-insensitive colation? Just a WHERE tag = 'tag' should use index and be really fast. 12000 rows should be not enough to worry about performance with proper indexing and sensible queries.
    – jkavalik
    Nov 11, 2015 at 20:36
  • Yes I had indices but I am not sure if they were right. The tags table was case sensitive because some users wanted to have 'ArtistNamE' or something and others perhaps 'artistname'. It was slower because I want to show all photos of the same TAG and tag and taG summarized. The get-the-pics-query went from about 6-10ms to 2-3ms now, i think. I had to get all tag-ids and then all photos with WHERE IN (1, 2..) . And updating tags is easier now. Perhaps you are right. I just thought I found I better way with VARCHAR or SET...
    – haheute
    Nov 11, 2015 at 21:31
  • 1
    You can have columns with different collations in the same table. And the way the string is stored does not change with a collation, so you can have ArtistName for one photo and artistNAME for other one with the (photo_id, tag) table and still search all combinations. And you should use JOIN to photos, not WHERE IN - SELECT <photo columns> FROM photos p JOIN tags t USING(photo_id) WHERE t.tag = 'artistname' GROUP BY photo_id
    – jkavalik
    Nov 12, 2015 at 6:11
  • I think I had something like this (2 queries): SELECT id FROM tags WHERE tag COLLATE(utf-8-unicode-ci) tag and then SELECT pic_columns FROM pic_tag JOIN pics ON pics.id = pic_tag.pic_id WHERE pic_tag.tag_id IN ($tag_ids) I will try it with just a (photo_id, tag) table like you wrote.
    – haheute
    Nov 13, 2015 at 8:33

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.