Hi Forum,
my name is Thomas and i’m new here.
I have a question about design for search engine on a platform where the given politic is to use mysiam engine.
No i designed a search engine for the given data and i don’t know if this on is really a good choice or if any body has better ideas.
DB design:
takes the words and the link to the table this word is found in
CREATE TABLE suchindex (
id int(10) unsigned NOT NULL auto_increment,
wort varchar(64) collate latin1_german2_ci NOT NULL default ‘’,
tabelle char(cool: collate latin1_german2_ci default NULL,
count int(11) default NULL,
PRIMARY KEY (id),
UNIQUE KEY u_wort (wort)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
HOLDS ALL ITEMS AND THE SUCHINDEX ID
CREATE TABLE artikelindex (
id int(10) unsigned NOT NULL auto_increment,
item_id int(10) unsigned NOT NULL default ‘0’,
suchindex_id int(10) unsigned NOT NULL default ‘0’,
PRIMARY KEY (id),
UNIQUE KEY u_ite, (item_id,suchindex_id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
IS USED TO CREATE A NEW TABLE LIKE WORD_[a-z0-9]
in word_a the wort_id is the reference to suchindex.id and item_id the reference to the item.id in the current database
CREATE TABLE default_wort (
id int(10) unsigned NOT NULL auto_increment,
wort_id int(10) unsigned NOT NULL default ‘0’,
item_id int(10) unsigned NOT NULL default ‘0’,
PRIMARY KEY (id),
UNIQUE KEY u_item (wort_id,item_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
Backtrace to the user if a user_search is performed
CREATE TABLE items_currently_in_search (
item_id int(10) unsigned NOT NULL default ‘0’,
user_id mediumint(cool: unsigned NOT NULL default ‘0’,
PRIMARY KEY (item_id),
KEY status (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
IS ONLY USED IF A FULLTEXT SEARCH IS SEND
CREATE TABLE titlefulltext (
id int(10) unsigned NOT NULL auto_increment,
title varchar(155) collate latin1_german2_ci default NULL,
item_id int(10) unsigned default NULL,
PRIMARY KEY (id),
UNIQUE KEY u_item (item_id),
FULLTEXT KEY title (title)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
IS ONLY USED IF A FULLTEXT SEARCH IS SEND LIKE “give me a hand”
CREATE TABLE descfulltext (
id int(10) unsigned NOT NULL auto_increment,
description text collate latin1_german2_ci,
item_id int(11) NOT NULL default ‘0’,
PRIMARY KEY (id),
UNIQUE KEY u_item (item_id),
FULLTEXT KEY desc (description)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
Now i’m using Perl to fill the table, what takes a long time because i have to perform some =~s for data i want to get saved (like aso.)
I have this design online and the search performance on the online page is okay (150.000 different items with a avg length of 814,3308 and max length of 120.000 signs ) now i will port this design to a web page how has 10 times of items and the length of them in avg is a little higher.
Anybody can see if the performance of my design can hold this increase of information?
Thanks to anybody how can tell me something.