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? _________________ Tore
_________________________________________________________________
Hotmail snakker ditt språk! http://www.hotmail.msn.com/cgi-bin/sbox?rru=dasp/lang.asp - Få Hotmail på norsk i dag
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
