Professional Web Applications Themes

Searching on keywords - MySQL

Hello, I have a table setup as: +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | EID | int(11) | YES | | NULL | | | Keyword | text | YES | MUL | NULL | | +---------+---------+------+-----+---------+-------+ the Keyword field contains comma delimited words ie: nascar,500,races When a user is searching the database what is the best and fastest method for searching all the words in the Keyword fields for a match? this is what i have now: select * from Keywords where match(Keyword) against('+"nascar"' in boolean mode); Is this query ...

  1. #1

    Default Searching on keywords

    Hello,
    I have a table setup as:
    +---------+---------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+---------+------+-----+---------+-------+
    | EID | int(11) | YES | | NULL | |
    | Keyword | text | YES | MUL | NULL | |
    +---------+---------+------+-----+---------+-------+

    the Keyword field contains comma delimited words ie: nascar,500,races
    When a user is searching the database what is the best and fastest method
    for searching all the words in the Keyword fields for a match?

    this is what i have now:
    select * from Keywords where match(Keyword) against('+"nascar"' in
    boolean
    mode);
    Is this query ok? any help would be great.

    -Paul


    Paul Cartier Guest

  2. #2

    Default Re: Searching on keywords


    Paul Cartier wrote:
    > When a user is searching the database what is the best and fastest method
    > for searching all the words in the Keyword fields for a match?
    Restructure the database! Don't comma separate the keywords. Each
    keyword should have its own id.

    So, have a separate table for keywords(keyword_id,keyword), and another
    that relates keywords to EID, so eid_keyword_id(eid,keyword_id) - where
    the primary key is a combination of both IDs. Also, index the 'keyword'
    field. Oh, and don't call it eid, that's going to drive you nuts one
    day!

    strawberry Guest

  3. #3

    Default Re: Searching on keywords

    Thanks, for the help, I did see this method used once before.
    I will give it a try, the keyword database comes from an xml feed that
    contains and ID and Keywords (comma delimited), I wll just have to
    seperate the keywords before inserting into mysql.

    Thanks for your help
    -Paul

    On Sat, 24 Jun 2006, strawberry wrote:
    >
    >
    >
    > Paul Cartier wrote:
    >
    >> When a user is searching the database what is the best and fastest method
    >> for searching all the words in the Keyword fields for a match?
    >
    > Restructure the database! Don't comma separate the keywords. Each
    > keyword should have its own id.
    >
    > So, have a separate table for keywords(keyword_id,keyword), and another
    > that relates keywords to EID, so eid_keyword_id(eid,keyword_id) - where
    > the primary key is a combination of both IDs. Also, index the 'keyword'
    > field. Oh, and don't call it eid, that's going to drive you nuts one
    > day!
    >
    >
    Paul Cartier Guest

Similar Threads

  1. Different Keywords per page
    By Podsnap in forum Macromedia Contribute General Discussion
    Replies: 2
    Last Post: September 20th, 07:17 AM
  2. keywords for searching archives
    By wrecks in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 2
    Last Post: April 1st, 08:52 PM
  3. keywords and .swf
    By in forum Web Design
    Replies: 1
    Last Post: March 7th, 05:37 AM
  4. Keywords
    By kf2003 in forum Web Design
    Replies: 2
    Last Post: October 22nd, 03:46 PM
  5. MySql search question. Searching on keywords
    By dpg in forum PHP Development
    Replies: 1
    Last Post: August 9th, 06:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139