Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Creating the Tables
A full search engine works by creating an index that associates three things: the unique pages on a site, the unique words on a site, and how many times each word appears on each page. Those tables can be defined as:
CREATE TABLE se_pages (
page_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
page_url VARCHAR(100) NOT NULL,
PRIMARY KEY (page_id),
UNIQUE (page_url)
);
CREATE TABLE se_words (
word_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
word VARCHAR(25) NOT NULL,
PRIMARY KEY (word_id),
UNIQUE (word)
);
CREATE TABLE se_pages_words (
page_id INT UNSIGNED NOT NULL,
word_id INT UNSIGNED NOT NULL,
frequency TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (page_id, word_id)
);
Each unique page on the site gets represented as a record in the se_pages table. Each unique word gets represented as a record in the se_words table. And the se_pages_words table is an intermediary between those two, reflecting the number of times a word appears on a given page. This table will allow the system to rank search results appropriately.