Welcome to WebmasterWorld Guest from 54.92.153.90

Forum Moderators: open

Message Too Old, No Replies

Need tips on adding a search box to my site

     
7:05 pm on Nov 6, 2014 (gmt 0)

Junior Member

5+ Year Member

joined:Aug 21, 2008
posts:139
votes: 1


I present videos on my site. The videos' details are stored in two MySQL InnoDB tables:

Video_Table
video_id
category_id (foreign key constraint)
video_title
video_description
video_thumbnail
video_views

Category_Table
category_id
category_name

I want to add a search feature to my site so that users can search for specific videos. I want the script to search through:

video_title (UNIQUE index)
video_description (no index)

and present any videos that match the search query.

The best way to do this would be to add FULLTEXT indexes to these columns, but I can't because FULLTEXT indexes are not available on my version of MySQL, and I can't upgrade to the version that supports them (5.6.4) because I'm on shared hosting. :-/


Questions

  1. What should I do? Should I keep everything as it is and just use LIKE '%...%' statements? Note that, with just 3,000 rows, my video table is relatively small, and I doubt that it'll grow more than 8,000 rows.
  2. Should I add an index to video_description? If so, what kind?
  3. How should I prepare the search queries? I don't want to just do this: "SELECT ... WHERE (video_title LIKE '%.$query.%') OR (video_description LIKE '%.$query.%')"
8:12 pm on Nov 7, 2014 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Apr 30, 2008
posts:2630
votes: 191


Have you done some performance timing?

You can build the test table that would have 10000 rows (your predicted maximum + 25%) and see how long it would take using SELECT and LIKE. Maybe the timings is acceptable, so you do not need to change anything.
6:17 pm on Nov 24, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2610
votes: 0


I have been creating word indexes for searching. It works really well, but you lose a little performance at time of inserting/updating your items but you gain performance at time of search.

word_index
id | word | obj_type | obj_id

1 | the | video | 20
2 | cat | video | 20
3 | jumps | video | 20
4 | cat | category | 43
5 | cute | category | 43

You can create a text index on the word field (make sure to do this). Anytime a video or category is saved you delete all the words indexed against that object and then parse through all the words being saved in the description, title and any other field you want users to be able to search against and insert each word back into the index. (this is where you will lose some performance, but if only admins save content then it is a good trade off)

When users perform a search, you search the words against the word index table... count up how many ids of each type are returned and then you return your search results from that. (this is where you gain performance, especially with a text index)

Some advantages of this is you can count how many times each word is returned for each object and sort on that... usually more matches equals more relevance.

You can combine search results for categories, videos and any other objects you want to index but perform all searches against 1 table

You can give users the option to select if they are searching for things of type video or type category and you just pass in an extra arg and the condition is a where clause rather than if statements around which tables to search.

One downside is "true" full phrase searching needs to still happen against the tables themselves, however I have found even when searching phrases that searching each word and counting up all the matches still returns relevant results. Testing will reveal if that is a problem for you though, so far it hasn't been for me.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members