Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: help with full-text search

    hi,

    I want to perform full-text search in the tables shown below.
    What I would like is to search for restaurants rows having all or some of the search strings in any fields or the comments.

    eg: "japanese cheap amazing barcelona"

    I don't know how to perform a full text search, knowing that some of the columns are ID (e.g. city_id) with name stored in separate tables and that the comments are in a separate table.

    Any help or pointers?

    Thanks


    mysql>describe restaurants;
    +---------------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------+------------------+------+-----+---------+----------------+
    | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
    | name | varchar(50) | YES | | NULL | |
    | street_number | int(11) | YES | | NULL | |
    | street | varchar(50) | YES | | NULL | |
    | postal_code | varchar(8) | YES | | NULL | |
    | city_id | int(11) | YES | | NULL | |
    | country_id | int(11) | YES | | NULL | |
    | link | varchar(80) | YES | | NULL | |
    | opening_hours | varchar(80) | YES | | NULL | |
    | price_range | int(11) | YES | | NULL | |
    | type_id | int(11) | YES | | NULL | |
    +---------------+------------------+------+-----+---------+----------------+


    mysql> describe comments;
    +----------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+------------------+------+-----+---------+----------------+
    | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
    | user_id | int(10) unsigned | NO | | NULL | |
    | resto_id | int(10) unsigned | NO | | NULL | |
    | comment | varchar(500) | YES | | NULL | |
    +----------+------------------+------+-----+---------+----------------+

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'll need a FULLTEXT index on each table

    there's lots more good information in da manual -- you need to read it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    2
    Thanks, but my question is more about how to perform the search across the different tables (I know I must add indexes).

    More explicitely, I want to find the restaurants having:
    -all or some of the search strings in any text fields (text, varchar...) of the 'restaurants' table
    -'city_id' and/or 'country_id' corresponding to cities or countries name matching some of the search strings
    -their ids in rows of the 'comments' table which 'comment' field contain some of the search strings.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by julj
    Any help or pointers?
    sure

    - don't use fulltext searching on VARCHAR columns if LIKE is more appropriate
    - consider searching individual tables separately instead of as part of a join
    - don't search numeric columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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