HELP: most effective tablestructure/indexes??

[Tore]

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?

[Tore]

See original question!!!

[Barand]

I\'d do it this way

 

thetable

---------

TheID, primary key

MemID,

other fields

INDEX on Memid

 

theKeywords

---------------

TheID,

Keyword

INDEX on TheID

INDEX on Keyword

 

SELECT TheID FROM theKeywords WHERE Keyword LIKE \'theWord%\'

 

hth

[Tore]

Could you please say why you would do it that way,

and why you prefer to not use a field that hold all the

words and then use a fulltext index on that field.

 

Is this solution faster when finding related tables?

Or is there som other reason why you prefer this solution?

[Barand]

I think it would be faster where mysql just has to search an index for a word rather than look for a word amongs several. FULLTEXT seems a large overhead for a keyword search given that you want the keywords in a separate table.

[Tore]

Hmmm... Do not know what to say.

 

I appreciate your answer, but now I have two different answers to my question. I have posted the same question on three different sites.

 

On one other site a guy say that I should go for the FULLTEXT solution.

 

I to thought it would be faster to store it the way you preferred and not use the FULLTEX solution. But what I think and how it works in real life situation may be different.

 

I might have to send the question to MySql AB ?

 

Anyway thanks again.

[Barand]

I suppose it comes down to how keywords are decided.

 

If users enter the keywords relevant to the record at the time it is submitted to the database, and you want searches limited to those keywords, go with the solution of separate indexed table.

 

If keywords can be anything in the text, go with FULLTEXT.

 

Does this sound reasonable?