MySQL Performance: FullText => DB Design for search

DB Design for search [message #1551]

Wed, 25 July 2007 05:10

shivaball
Messages: 3
Registered: July 2007

Junior Member

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 <script> 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.

Report message to a moderator