해시태그를 이용한 검색 최적화

커뮤니티나 쇼핑몰을 운영하다보면 사용자가 원하는 내용을 좀더 정확히 보여주기를 원한다.
이럴경우 like 문을 주로 사용하게 되는데 가령 쇼핑몰에서 상품명, 상품내용, 모델, 브랜드, 제조사.. 수많은 컬럼들을 모두 like문을 사용한다면 검색에서 상당한 트래픽이 발생하게 되고 또한 필요없는 내용도 상당수 노출되게 됩니다.
이럴 경우 사용자가 제품 혹은 글을 등록할때 특정 키워드를 입력하는 방식을 사용한다면 유용하게 처리가능할 것입니다.
아래는 이런 시스템을 구성하고 활용하는 방법에 대해 간략히 설명 드리려고 합니다.

테이블 구성

tags 테이블

태그 테이블은 키워드를 넣어두는 용이다. 이 부분은 Insert 만 존재하며 delete는 하면 안된다.
일종의 단어북 이라고 여기면 된다. update도 거의 하지 않는 것이 좋지만 잘못된 문자가 들어가 있거나 하면 간혹 update는 필요하다.

post_tags 테이블

post 가 삭제될때 같이 삭제되는 것이 좋으므로 제약 조건을 걸어 두는 것을 추천드립니다.

CONSTRAINT `FK_post_tags_posts` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE

작업 플로우

Create Post

post를 작성한다고 할때 title, content, tags 세개의 필드를 전송하게 됩니다.
이때 먼저 tags 를 tags 테이블을 참조하여 존재 하지 않는 경우는 insert 한후 각각의 id를 구합니다.
그리고 posts 테이블에 title, content를 입력 후 그 post id를 구합니다. 그리고 마지막으로 post id 와 tag의 id들을 이용해 post_tags 테이블에 입력하면 됩니다.

Delete Post

post 삭제시는 위처럼 post_tags 에 foreign key를 걸어 두었으므로 자동으로 삭제되지만 만약 걸어 두지 않았다면 post_tags 에 존재하는 삭제하려는 post 정보를 삭제 하여야 합니다.

Update Post

위 Create post와 작업방식은 유사하나 존재하던 tag가 사라졌을 수도 있으니 비교하여 처리하시기 바랍니다.
저는 post_tags 모두 삭제후 다시 insert하는 방식을 사용합니다.

Select Post

검색을 이용하여 특정 포스트를 찾을 경우 먼저 검색어를 분리하여(공백등이 있거나 하면 각각의 단어들로 분리) 하여 tags 테이블에서 id 값을 가져옵니다.

SELECT id 
FROM tags
WHERE tag IN ('keyword1', 'keyword2'....);

그리고 검색된 id를 이용하여 다시 post_tags 를 참조하고 이것을 이용하여 실제 포스트를 찾아 내는 방법입니다.

SELECT posts.* 
FROM post_tags
JOIN posts ON posts.id = post_tags.post_id
WHERE post_tags.tag_id IN (1, 2) // 여기서 1, 2는 위에서 구한 tags 의 id 값들