Small issue with FULLTEXT searches

--0016e6d644f505427704855f0531
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d644f505427704855f0531--

Re: Small issue with FULLTEXT searches

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 wrote:
> Hi List,
>
> Table structure:
> mysql> DESCRIBE FlightRoutes;
> +---------+------------+------+-----+---------+-------+
> | Field =A0 | Type =A0 =A0 =A0 | Null | Key | Default | Extra |
> +---------+------------+------+-----+---------+-------+
> | EntryID | char(36) =A0 | NO =A0 | PRI | NULL =A0 =A0| =A0 =A0 =A0 |
> | Dep =A0 =A0 | varchar(5) | NO =A0 | MUL | NULL =A0 =A0| =A0 =A0 =A0 |
> | Des =A0 =A0 | varchar(5) | NO =A0 | =A0 =A0 | NULL =A0 =A0| =A0 =A0 =A0=
|
> | Route =A0 | text =A0 =A0 =A0 | NO =A0 | =A0 =A0 | NULL =A0 =A0| =A0 =A0=
=A0 |
> +---------+------------+------+-----+---------+-------+
> 4 rows in set (0.01 sec)
>
> Indexes:
> mysql> SHOW INDEXES FROM FlightRoutes;
> +--------------+------------+------------+--------------+--- ----------+--=
---------+-------------+----------+--------+------+--------- ---+---------+
> | Table =A0 =A0 =A0 =A0| Non_unique | Key_name =A0 | Seq_in_index | Colum=
n_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment=
|
> +--------------+------------+------------+--------------+--- ----------+--=
---------+-------------+----------+--------+------+--------- ---+---------+
> | FlightRoutes | =A0 =A0 =A0 =A0 =A00 | PRIMARY =A0 =A0| =A0 =A0 =A0 =A0 =
=A0 =A01 | EntryID =A0 =A0 |
> A =A0 =A0 =A0 =A0 | =A0 =A0 =A0 21375 | =A0 =A0 NULL | NULL =A0 | =A0 =A0=
=A0| BTREE =A0 =A0 =A0| =A0 =A0 =A0 =A0 |
> | FlightRoutes | =A0 =A0 =A0 =A0 =A01 | ixAirports | =A0 =A0 =A0 =A0 =A0 =
=A01 | Dep =A0 =A0 =A0 =A0 |
> A =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A01943 | =A0 =A0 NULL | NULL =A0 | =A0 =
=A0 =A0| BTREE =A0 =A0 =A0| =A0 =A0 =A0 =A0 |
> | FlightRoutes | =A0 =A0 =A0 =A0 =A01 | ixAirports | =A0 =A0 =A0 =A0 =A0 =
=A02 | Des =A0 =A0 =A0 =A0 |
> A =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A07125 | =A0 =A0 NULL | NULL =A0 | =A0 =
=A0 =A0| BTREE =A0 =A0 =A0| =A0 =A0 =A0 =A0 |
> | FlightRoutes | =A0 =A0 =A0 =A0 =A01 | ixRoutes =A0 | =A0 =A0 =A0 =A0 =
=A0 =A01 | Dep =A0 =A0 =A0 =A0 |
> NULL =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 1 | =A0 =A0 NULL | NULL =A0 | =A0 =
=A0 =A0| FULLTEXT =A0 | =A0 =A0 =A0 =A0 |
> | FlightRoutes | =A0 =A0 =A0 =A0 =A01 | ixRoutes =A0 | =A0 =A0 =A0 =A0 =
=A0 =A02 | Des =A0 =A0 =A0 =A0 |
> NULL =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 1 | =A0 =A0 NULL | NULL =A0 | =A0 =
=A0 =A0| FULLTEXT =A0 | =A0 =A0 =A0 =A0 |
> | FlightRoutes | =A0 =A0 =A0 =A0 =A01 | ixRoutes =A0 | =A0 =A0 =A0 =A0 =
=A0 =A03 | Route =A0 =A0 =A0 |
> NULL =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 1 | =A0 =A0 NULL | NULL =A0 | =A0 =
=A0 =A0| FULLTEXT =A0 | =A0 =A0 =A0 =A0 |
> +--------------+------------+------------+--------------+--- ----------+--=
---------+-------------+----------+--------+------+--------- ---+---------+
> 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 tha=
t
> 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 whe=
n
> mySQL doesn't see / use the FULL TEXT that has already been created.
>
> Thanks for the assistance.
>
>
>
> --
>
> Regards,
> Chris Knipe
>



--=20
Baron Schwartz
Percona Inc
Consulting, Training, Support & Services for MySQL

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg