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 | |
+----------+------------------+------+-----+---------+----------------+

you'll need a FULLTEXT index on each table

there's lots more good information in da manual -- you need to read it

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.

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