visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Which table structure and index structure are best in this case??
By: FiatLink Belgium  Date: 23/11/2004 10:43:29  English French  Points: 50 Status: Answered
Quality : Excellent
Hi.

Have one table with FIXED row length.
Table name: "thetable".
Field 1: MemID (mediumint). (relates to another table members)
Field 2: TheID (smallint).
Primary key: MemID, TheID.
Lots of fields. Fixed table length.

I want to make it possible for users to search for records in this table related to one or more keywords.

Since I want thetable to be as small as possible I want to store the releated keywords in another table:

Solution A:
Either in a table called thekey with these fields, keys and indexes:
Field 1: MemID.
Field 2: TheID.
Field 3: Keywords (Varchar (255)).
Primary key: MemID, TheID.
Index on Keywords field as FULLTEXT index.

Solution B:
Or in a table called thekey with these fields, keys and indexes:
Field 1: MemID.
Field 2: TheID.
Field 3: Keyword (varchar (30)).
Primary key: MemID, TheID, Keyword.
Index on Keyword.

QUESTION 1:

Which of the two solutions will make the fastest select queries if I search like this:

Solution A: Searches would be done like this:
select TheID from thekey where match (Keywords) against ('theword');


Solution B: Searches would be done like this:
select TheID from thekey where Keyword like 'theword%';
or
select TheID from thekey where Keyword like 'theword';



Consider houndreds of thousands of records in table "thetable".

QUESTION 2:
Which of the two solutions will use less diskspace?
By: VGR Date: 23/11/2004 10:45:12 English  Type : Answer
Q1 : solution A
Q2 : solution A

That's because FULLTEXT search is VERY F.A.S.T. in MySql

how to set it up :

ALTER TABLE xxx ADD FULLTEXT(Keyword);
SELECT * FROM xxx WHERE MATCH(Keyword) AGAINST ('litteral value');

http://www.mysql.com/doc/en/Fulltext_Search.html
By: FiatLink Date: 23/11/2004 12:02:31 French  Type : Comment
merci.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 341.785910 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page