List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:April 29 2010 12:30pm
Subject:Re: Small issue with FULLTEXT searches
View as plain text  
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
Thread
Small issue with FULLTEXT searchesChris Knipe29 Apr
  • Re: Small issue with FULLTEXT searchesBaron Schwartz29 Apr