MySQL Forums
Forum List  »  Full-Text Search

full-text index not being used
Posted by: C. Suhrmann
Date: August 23, 2007 11:00AM

Hi,

I am using MySql 5.0.33 on Windows and I want to perform a fulltext-search on more than one column.

My table looks like this:

CREATE TABLE `messages` (
  `id` int(11) NOT NULL auto_increment,
  `fromIdentifier` varchar(255) collate latin1_general_ci NOT NULL,
  `fromName` varchar(255) collate latin1_general_ci NOT NULL,
  `toIdentifier` varchar(255) collate latin1_general_ci NOT NULL,
  `toName` varchar(255) collate latin1_general_ci NOT NULL,
  `subject` varchar(255) collate latin1_general_ci NOT NULL,
  `text` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `fulltext_from` (`fromIdentifier`,`fromName`),
  FULLTEXT KEY `fulltext_to` (`toIdentifier`,`toName`),
  FULLTEXT KEY `fulltext_subject` (`subject`),
  FULLTEXT KEY `fulltext_text` (`text`)
) ENGINE=MyISAM

I want to search for a string (e.g. "Portugal") in the fulltext-columns using this query:

EXPLAIN SELECT
    id                
FROM
    messages
WHERE
    (
        MATCH (text) AGAINST ('Portugal' IN BOOLEAN MODE)
        OR
        MATCH (toName, toIdentifier) AGAINST ('Portugal' IN BOOLEAN MODE)
    )

For some strange reason, mySql does not use any fulltext-index.

EXPLAIN outputs:
id  select_type   table     type  possible_keys   key   key_len   ref   rows   Extra
1   SIMPLE        messages  ALL   NULL            NULL  NULL      NULL  0      Using where

When I change the "OR" to "AND", EXPLAIN shows that the fulltext-index is being used:

id  select_type   table     type      possible_keys               key           key_len   ref   rows   Extra
1   SIMPLE        messages  fulltext  fulltext_to,fulltext_text   fulltext_to   0               1      Using where

What am I doing wrong? Can anyone help, please?

Thank you in advance!

Options: ReplyQuote


Subject
Views
Written By
Posted
full-text index not being used
4155
August 23, 2007 11:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.