MySQL - 全文搜索 - 丢失索引

Question

So here is the scenario:

• MySQL
• have 1 MYISAM Table
• colum named v.value has a full text index

Basic query works fine, uses the index as expected:

SELECT p.online_identifier
FROM (...)
WHERE  r.area_id = 3 AND s.state_id= 4
AND (snap.area_has_catalogues_attributes_id = 7028
AND MATCH (v.value) AGAINST('+SomeBrand' IN BOOLEAN MODE))

---

Now when I add an OR, the full text search index (on v.value) is not used. I run Explain to verify it.

The query would look something like this:

(...) WHERE r.area_id = 3 AND s.state_id= 4 AND (snap.area_has_catalogues_attributes_id = 7028 AND MATCH (v.value) AGAINST('+SomeBrand' IN BOOLEAN MODE)) OR (snap.area_has_catalogues_attributes_id = 7045 AND MATCH (v.value) AGAINST('+OtherBrand' IN BOOLEAN MODE))

---

I dont understand why. Any ideas?

Answer 1

Here is something interesting: The query you have is actually causing the FULLTEXT indexing to be ignored. Don't worry, it is not your fault. I wrote about this before : Is there a way to hint to query optimizer to MySQL which constraints should be done first?

After looking over my answer and its subsequent links, now let's look at your original query:

SELECT p.online_identifier
FROM (...)
WHERE  r.area_id = 3 AND s.state_id= 4
AND (snap.area_has_catalogues_attributes_id = 7028
AND MATCH (v.value) AGAINST('+SomeBrand' IN BOOLEAN MODE))

You will have to execute the FULLTEXT search alone and retrieve IDs only.

Use those IDs to join to the other table aliases.

---

Let me take a query from one of my other links to demonstrate what to do to your query

Instead of this query

select * from seeds WHERE MATCH(text) AGAINST
("mount cameroon" IN BOOLEAN MODE) = 4;

you must refactor it into something like this:

SELECT B.* FROM
(
    SELECT id,MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE) score
    FROM seeds
    WHERE MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE)
) A
INNER JOIN seeds B USING (id)
WHERE A.score = 4;

Give it a Try !!!