Jump to content


Photo

HELP: most effective tablestructure/indexes??


  • Please log in to reply
6 replies to this topic

#1 Tore

Tore
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationNorway

Posted 28 July 2003 - 05:53 PM

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

#2 Tore

Tore
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationNorway

Posted 29 July 2003 - 09:53 AM

See original question!!!
Tore

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 03 August 2003 - 10:11 AM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 Tore

Tore
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationNorway

Posted 03 August 2003 - 10:49 AM

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?
Tore

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 03 August 2003 - 10:59 AM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 Tore

Tore
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationNorway

Posted 06 August 2003 - 08:25 AM

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.
Tore

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 August 2003 - 05:26 PM

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?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users