Need tips on adding a search box to my site - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Need tips on adding a search box to my site

Tehuti

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

10+ Year Member

Top Contributors Of The Month

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

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.
Should I add an index to video_description? If so, what kind?
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.%')"

aakk9999

8:12 pm on Nov 7, 2014 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

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.

Demaestro

6:17 pm on Nov 24, 2014 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

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.