I have a mysql table called "videos" which holds a list of videos. I have another table called "tags" in which I insert tags related to that video, so the "tags" table looks something like this: [B]video_title | tag[/B] video1 | tag1 video1 | tag2 video1 | tag3 video2 | tag4 video2 | tag2 video3 | tag1 Code (markup): I have a lot of pages with a lot of videos (taken from the "videos" table) and every video is tagged like I shown above.On the page where my video is,I have a div called "Related videos" (like on YouTube) that should contain the script that would compare the tags of the video that is currently showing with all the videos that have the same tags like that one. So let's suppose that I have a "video3" in this case.It is marked with "tag1".The "Related videos" should contain all similar videos so, in this case, I would expect the "Related videos" to display the "video1" as it is also tagged with "tag1". I've created the query that does the job (using WITH function and IN operator) with this table,but when I got app.700 records my hoster said that I must instantly remove the query because it is slowing down the server,which I did and now I need some query that will do the same job but not to slow down server. I know that I haven't created quite a system that could handle that amount of records very fast and without slowing the server. So my question is how to create my tagging system (I'm sure you've done it already,I just need the drill) that would show related videos based on tags compared to the present one? Thanks!
This can work fast if you put an index on column tag (or over tag + video_title) and search for the exact word select video_title from videos where tag='tag1' it will never work fast if you use IN and tag1 can contain multiple words on which you search (using IN) or if you don't have an index on column tag