0

When i try the following query:

select co_id, Match(co_title,co_description,co_text) AGAINST ('word')  from (select * from content limit 100)co

the result is: #1191 -Can't find FULLTEXT index matching the column list

FULLTEXT indexes are set correctly :

when i try :

select co_id, Match(co_title,co_description,co_text) AGAINST ('word') from content

it works fine.

why the first query is not working?

john
  • 1,282
  • 1
  • 17
  • 30

1 Answers1

1

That's not about FULLTEXT only, actually - that is because you're referring to subquery (in FROM clause) which is runtime-created table (rowset) and have not any indexes, so FULLTEXT also as well.

I suggest this:

SELECT 
  co_id, 
  MATCH(co_title,co_description,co_text) AGAINST ('word') 
FROM 
  content 
LIMIT 100
Alma Do
  • 37,009
  • 9
  • 76
  • 105
  • in this case, i'm running a fulltext search on all rows then i'm extracting the first 100 rows. am i right? My point is not to search in all content table (my content table is huge). is this possible? – john Oct 01 '13 at 10:14
  • When using `LIMIT` - it's not fully equal to `FULL SCAN`, especially if it's about selecting _first N rows_. In this case DBMS will read & return only that N rows, which will be quick - despite of FULL SCAN execution plan – Alma Do Oct 01 '13 at 10:21