Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

DB Design for search

shivaballshivaball EntrantCurrent User Role Beginner
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.

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    Do not use your own "word" tables. this will make search slow hard to maintain and hard to deal with complicated queries you may need to handle.

    MyISAM Full Text Search is better choice for small/medium size projects when search speed is acceptable.
  • shivaballshivaball Entrant Current User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">
    Do not use your own "word" tables. this will make search slow hard to maintain and hard to deal with complicated queries you may need to handle.

    MyISAM Full Text Search is better choice for small/medium size projects when search speed is acceptable.
    </td></tr></table>

    This is the problme, the FULLTEXR tables are not anymore performed a search by against match on a short word (2 signes) takes to long

    Thanks
    Thomas
  • amriamri Entrant Inactive User Role Beginner
    Hi. Test with separated tables.Maybe this is the decision.Have a nice day.
  • shivaballshivaball Entrant Current User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.