Chris,
The index on (Dep, Des) is not a full-text index. If you use SHOW
CREATE TABLE, I think this becomes much easier to see at a glance.
- Baron
On Thu, Apr 29, 2010 at 8:10 AM, Chris Knipe <savage@stripped> wrote:
> Hi List,
>
> Table structure:
> mysql> DESCRIBE FlightRoutes;
> +---------+------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +---------+------------+------+-----+---------+-------+
> | EntryID | char(36) | NO | PRI | NULL |
> |
> | Dep | varchar(5) | NO | MUL | NULL |
> |
> | Des | varchar(5) | NO | | NULL |
> |
> | Route | text | NO | | NULL
> | |
> +---------+------------+------+-----+---------+-------+
> 4 rows in set (0.01 sec)
>
> Indexes:
> mysql> SHOW INDEXES FROM FlightRoutes;
>
> +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index |
> Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
> +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | FlightRoutes | 0 | PRIMARY |
> 1 | EntryID |
> A | 21375 | NULL |
> NULL | | BTREE |
> |
> | FlightRoutes | 1 | ixAirports |
> 1 | Dep |
> A | 1943 | NULL
> | NULL | | BTREE |
> |
> | FlightRoutes | 1 | ixAirports |
> 2 | Des |
> A | 7125 | NULL
> | NULL | | BTREE |
> |
> | FlightRoutes | 1 | ixRoutes |
> 1 | Dep |
> NULL | 1 | NULL
> | NULL | | FULLTEXT | |
> | FlightRoutes | 1 | ixRoutes |
> 2 | Des |
> NULL | 1 | NULL
> | NULL | | FULLTEXT | |
> | FlightRoutes | 1 | ixRoutes |
> 3 | Route |
> NULL | 1 | NULL
> | NULL | | FULLTEXT | |
>
> +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> 6 rows in set (0.00 sec)
>
> Query:
> mysql> SELECT Dep, Des, Route, MATCH(Dep, Des) AGAINST('FACT,OMDB') AS
> Relevance FROM FlightRoutes;
> ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
>
> I don't get that.
>
> What I have is a bunch of records:
> Point A, Point B, Route 1
> Point A, Point B, Route 2
> Point A, Point B, Route 2
> Point A, Point B, Route 3
>
> What I want to achive, is to select the Route from Point A to Point B that
> has the most relavence.... Naturally, I'm just starting to play with this
> now, but I fail to see how I can possibly play with FULL TEXT indexes when
> mySQL doesn't see / use the FULL TEXT that has already been created.
>
> Thanks for the assistance.
>
>
>
> --
>
> Regards,
> Chris Knipe
>
--
Baron Schwartz
Percona Inc <http://www.percona.com/>
Consulting, Training, Support & Services for MySQL
