# DB Design for search

**URL:** <https://forums.percona.com/t/db-design-for-search/2065>\
**Category:** Sphinx & Full-Text Search\
**Created:** [July 25, 2007, 3:40am UTC](https://forums.percona.com/t/db-design-for-search/2065 "2007-07-25T03:40:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![shivaball](https://avatars.discourse-cdn.com/v4/letter/s/76d3ee/32.png) [@shivaball](https://forums.percona.com/u/shivaball)\
**Post date:** [July 25, 2007, 3:40am UTC](https://forums.percona.com/t/db-design-for-search/2065/1 "2007-07-25T03:40:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [August 16, 2007, 3:58am UTC](https://forums.percona.com/t/db-design-for-search/2065/2 "2007-08-16T03:58:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![shivaball](https://avatars.discourse-cdn.com/v4/letter/s/76d3ee/32.png) [@shivaball](https://forums.percona.com/u/shivaball)\
**Post date:** [August 16, 2007, 7:39am UTC](https://forums.percona.com/t/db-design-for-search/2065/3 "2007-08-16T07:39:48Z")

</div>

| [B]Quote:[/B] |
| 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.

 |

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

---

<div class="post-metadata">

**Author:** ![amri](https://avatars.discourse-cdn.com/v4/letter/a/e8c25b/32.png) [@amri](https://forums.percona.com/u/amri)\
**Post date:** [November 30, 2007, 7:45am UTC](https://forums.percona.com/t/db-design-for-search/2065/4 "2007-11-30T07:45:11Z")

</div>

Hi. Test with separated tables.Maybe this is the decision.Have a nice day.

---

<div class="post-metadata">

**Author:** ![shivaball](https://avatars.discourse-cdn.com/v4/letter/s/76d3ee/32.png) [@shivaball](https://forums.percona.com/u/shivaball)\
**Post date:** [December 4, 2007, 4:41pm UTC](https://forums.percona.com/t/db-design-for-search/2065/5 "2007-12-04T16:41:22Z")

</div>

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