Page 1 of 1

Developing an internal search engine

Posted: Tue Nov 14, 2006 4:54 am
by eski009
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I am trying to create an internal search engine.

I have about 100,000 different restaurants in a database with each one having about 5 different keywords.
I have put all the unique keywords in to a table on different rows.

I have then created another table with just id's to reference each word with each listing (this table has over 600,000 rows)

I am trying to think of a suitable FAST query to grab the data.

I am currently using the following code which depending on how many keywords the user enters adds the same table a number of times.

This isn't that quick.
Any ideas?

I am effectively trying to create a web2.0 tagging system like you see on flickr and del.icio.us.
Thanks.

Code: Select all

$searchquery = "
select a.title, a.description
from search_pages a";

for ($p=0; $p<$search_length; $p++)
		{
			$searchquery .= ", search_term_page_link ".$letter2_array[$p];
			$searchquery .= ", search_terms ".$letter_array[$p];
		} 
 

//$searchquery .= " where c.id = a.site_cat_id ";

	for ($p=0; $p<$search_length; $p++)
		{
			if ($p==0)
				$searchquery .= " where ";
			else
				$searchquery .= " and ";
				
			$searchquery .= " a.site_id = ".$letter2_array[$p].".site_id ";
			$searchquery .= " and ".$letter2_array[$p].".term_id = ".$letter_array[$p].".id ";
			$searchquery .= " and ".$letter_array[$p].".term = '".$search_query[$p]."' ";
		}

$searchquery .= " LIMIT 20";

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Nov 14, 2006 8:03 am
by John Cartwright
It would help to see an example query of yours.