CODEWITHSUNDEEP
mysql
DMJ
DMJ

Reputation: 854

Why is the MySQL MATCH function returning a relevance score of 0, but is considered true?

I have the following table in MySQL:

CREATE TABLE events(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(128) NOT NULL,
`datetime` DATETIME  NOT NULL,
`duration` TIME  NULL,
`websiteUrl` VARCHAR(128) NULL,
`location` VARCHAR(128) NULL,
`mapUrl` VARCHAR(256) NULL,
`summary` TEXT NULL,
`text` TEXT NULL,
`thumbUrl` VARCHAR(128) NULL,
`imgUrl` VARCHAR(128) NULL,
`created` DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated` DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FULLTEXT(title),
FULLTEXT(summary),
FULLTEXT(text),
INDEX(created)
) ENGINE = INNODB  DEFAULT CHARSET = utf8;

INSERT INTO `events` 
(
    `id`, 
    `title`, 
    `summary`, 
    `text`, 
    `datetime`, 
    `duration`, 
    `location`, 
    `mapUrl`, 
    `websiteUrl`, 
    `thumbUrl`, 
    `imgUrl`, 
    `created`, 
    `updated`, 
    `author`
) VALUES (
    1, 
    'Stuff', 
    'Some weird stuff is gonna happen', 
    '<p>You don't even want to know<br></p>\n', 
    '2023-10-14 23:41:00', 
    '09:01:33', 
    NULL, 
    NULL, 
    NULL, 
    'upload/events/thumb/newstuff5f28482474c9d.jpg', 
    'upload/events/img/newstuff5f28482477dab.jpg', 
    '2020-08-03 11:23:48',
    '2020-08-03 11:23:48',
    NULL
);

I wanted to implement a simple search function using the fulltext indexes. I ran the following query against the table, just to test and make sure my SQL code was working:

SELECT 
    "events" AS `table`,
    `id` AS `id`,
    "stuff" AS `query`,
    "events" AS `category`,
    `thumbUrl` AS `thumbnail`, 
    `datetime` AS `timestamp`, 
    `title` AS `title`, 
    `summary` AS `summary`, 
    `text` AS `content`, 
    ( MATCH (`title`) AGAINST ("stuff" IN NATURAL LANGUAGE MODE) * 10 
    + MATCH (`summary`) AGAINST ("stuff" IN NATURAL LANGUAGE MODE) * 3 
    + MATCH (`text`) AGAINST ("stuff" IN NATURAL LANGUAGE MODE) * 1 + 0 
    ) AS `relevance`
FROM `events`
WHERE (
    MATCH (`title`) AGAINST ("stuff" IN NATURAL LANGUAGE MODE) 
    OR MATCH (`summary`) AGAINST ("stuff" IN NATURAL LANGUAGE MODE) 
    OR MATCH (`text`) AGAINST ("stuff" IN NATURAL LANGUAGE MODE)
)

The intent here was to weight the relevance score of the title above that of the summary, and the summary above that of the main text. I have used very similar queries against very similar tables before, and normally it seems to work fine. However, on this table it seems to be action very strange. I get the following error:

SQLSTATE[22003]: Numeric value out of range: 1690 DOUBLE value is out of range in '((match `test`.`events`.`summary` against ('stuff')) * 3)'

If I change the query to not perform any mathematical operations on the relevance scores, and just return them raw, the scores for all three match statements are 0. However, the result of ORing together all three of those supposed 0s is 1, which makes me think they are not actually 0. To confirm that suspicion, I tested all three configurations individually, and despite all three match statements apparently returning 0, the match statements for the title and summary columns are considered true when used as a boolean.

Changing the query doesn't help, unless I change it to something that doesn't match any rows.

Can anyone explain to my why this is happening?

Upvotes: 0

Views: 206

Answers (1)

DMJ
DMJ

Reputation: 854

I found my answer here after typing out the long question above. Essentially, the reason I was getting that weird result was because I only had one row in the table I was using for testing. There is apparently a 50% rule in MySQL, where words that appear in 50% or more of rows in the full text index are ignored. Since there was only one row in my table, the full text index was essentially useless since all words appeared in 100% of rows.

Decided to post the question anyway, just because the question that eventually solved it for me was a little obscure and hard to find. Hopefully the next person who has the issue can find this question and not have to spend literally a whole day puzzling over something that will be a non-issue once the table gets some real data in it.

Upvotes: 1

Related Questions