| 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( 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( 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.
|
|
|
|
|
|
| Re: DB Design for search [message #2286 is a reply to message #1551 ] |
Fri, 30 November 2007 08:15  |
amri
Messages: 1
Registered: November 2007 |
Junior Member |
|
|
Hi. Test with separated tables.Maybe this is the decision.Have a nice day.
Web Design, Web Development-> http://www.dhstudio.eu
|
|
|
| Re: DB Design for search [message #2324 is a reply to message #2286 ] |
Tue, 04 December 2007 17:11 |
shivaball
Messages: 3
Registered: July 2007 |
Junior Member |
|
|
Hi Amri;
i change the hole design to sphinxsearch - for me the best, only the live update i need isn't there but with the delta shema i found a workaround, until the real live update is online for shpinxsearch
Bye Thomas
|
|
|
Members
Search
Help
Register
Login
Home
