From: ryan@paymentalliance.net
To: pgsql-hackers@postgresql.org
Subject: PostgreSQL Search Engine - searchraw.php3 (0/1)
Date: Thu, 15 Mar 2001 22:59:41 GMT
Message-ID: <3ab1437a.793029325@news.postgresql.org> (raw)
I have written a simple search engine that utilizes pgsql. I would
like to make it a stored procedure, although I am still learning
plpgsql. Attached to this message is the basic search logic (in PHP).
This script will be in production on a major e-commerce site in about
a week. I think it would be much faster as a stored procedure.
This implemetation searches product data, but it could be used for
anything.
The script does a few things, gets the id's of valid words, finds
which products these words are mapped to, finds which products have
the most keyword mappings out of the result set, and outputs a result
set joined to the product table and ordered by products with the most
keyword hits.
It accomplishes this by keeping an indexed list of words, maintaing a
mapping table to products. Then when someone does a search, a
temporary table called 'hits' is created which stores the product_id
that was matched to a word. Then an additional temporary table is
created which consists of the product id and hit count from the
search. The search then retrns the product details ordered by the
product that had the most hits.
If you are interested in seeing it in action, I can send you a url, if
you'd like to implement it I can help you out, and if you can help me
covert it to a stored procedure I'd be very apreciative!
It uses five tables:
CREATE TABLE "pa_search_keyword" (
"keyword_id" int4 DEFAULT
nextval('"pa_search_keywor_keyword_id_seq"'::text) NOT NULL,
"keyword_value" varchar(30),
CONSTRAINT "pa_search_keyword_pkey" PRIMARY KEY ("keyword_id")
);
and
CREATE TABLE "pa_search_map" (
"keyword_id" int4 NOT NULL,
"product_id" int4 NOT NULL,
CONSTRAINT "pa_search_map_pkey" PRIMARY KEY ("keyword_id",
"product_id")
);
two temporary tables:
CREATE TEMPORARY TABLE hits
(product_id integer not null);
and
CREATE TEMPORARY TABLE prod_hit_count (
product_id integer not null,
hit_count smallint not null
);
the fifth table would be the table you are joining to to get the
product data, or details, or whatever.
-Ryan Mahoney
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ryan@paymentalliance.net
Subject: Re: PostgreSQL Search Engine - searchraw.php3 (0/1)
In-Reply-To: <3ab1437a.793029325@news.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox