全文索引和复合索引以及它们如何影响查询

只是说我有一个查询如下..

SELECT 
    name,category,address,city,state
FROM
    table
WHERE 
    MATCH(name,subcategory,category,tag1) AGAINST('education')
AND 
    city='Oakland'
AND
    state='CA' 
LIMIT
    0, 10;

..我有一个fulltext索引为name,subcategory,category,tag1 and a composite索引为city,state;这对于这个查询来说足够好了吗？只是想知道混合额外的东西时是否需要额外的东西AND当使用带有 MATCH/AGAINST 的全文索引时。

Edit:我想要了解的是，查询中但未在所选索引（全文索引）中建立索引的附加列会发生什么情况，上面的示例是city and state。 MySQL 现在如何找到这些匹配的行，因为它不能使用两个索引（或者可以？） - 所以，基本上，我试图了解 MySQL 如何查找数据最佳地对于不在所选全文索引中的列，如果有什么我可以或should做优化查询。

如果我理解你的问题，你就会知道 MATCH AGAINST 使用你的 FULLTEXT 索引，并且你想知道 MySQL 如何应用 WHERE 子句的其余部分（即它是否执行表扫描或索引查找）。

这是我对您的表的假设：它在某些 id 列和 FULLTEXT 索引上有一个主键。

所以首先，MySQL 将never对城市/州 WHERE 子句使用 FULLTEXT 索引。为什么？因为 FULLTEXT 索引仅适用于 MATCH AGAINST。看here http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html在第一组项目符号（不是目录项目符号）之后的段落中。

EDIT:在您的情况下，假设您的表不仅有 10 行，MySQL 将为您的 MATCH AGAINST 应用 FULLTEXT 索引，然后对这些结果进行表扫描以应用城市/州 WHERE。

那么如果将 BTREE 索引添加到城市和州会怎样呢？

CREATE INDEX city__state ON table (city(10),state(2)) USING BTREE;

那么MySQL只能用one该查询的索引，因为它是一个简单的选择。它会either使用全文orBTREE。请注意，当我说一个索引时，我指的是一个索引定义，而不是多部分索引中的一列。无论如何，这就引出了一个问题：哪一个does it use?

这取决于表格分析。 MySQL 将尝试estimate（基于最后一个 OPTIMIZE TABLE 的表统计信息）哪个索引将修剪最多的记录。如果城市/州 WHERE 使您减少到 10 条记录，而 MATCH AGAINST 只能使您减少到 100 条记录，那么 MySQL 将使用 city__state 索引first对于城市/州 WHERE，然后对 MATCH AGAINST 进行表扫描。

另一方面，如果 MATCH_AGAINST 让您减少到 10 条记录，而城市/州 WHERE 让您减少到只有 1000 条记录，那么 MySQL 将首先应用 FULLTEXT 索引，然后对城市和州进行表扫描。

底线是基数你的索引。本质上，将进入索引的值有多独特？如果表中的每条记录都将城市设置为奥克兰，那么它不是一个非常唯一的键，因此城市 = '奥克兰'并没有真正为您减少那么多记录数量。在这种情况下，我们说您的 city__state 索引有低基数.

因此，如果您的 FULLTEXT 索引中 90% 的单词是“John”，那么出于完全相同的原因，这对您也没有多大帮助。

如果你能负担得起空间和 UPDATE/DELETE/INSERT 开销，我建议添加 BTREE 索引并让 MySQL 决定他想要使用哪个索引。根据我的经验，他通常能很好地挑选合适的人选。

我希望这能回答你的问题。

EDIT:顺便说一句，请确保为 BTREE 索引选择正确的大小（在我的示例中，我选择了 city 中的前 10 个字符）。这显然对基数产生了巨大影响。如果您选择 city(1)，那么显然您将获得比选择 city(10) 更低的基数。

EDIT2:MySQL 的索引修剪最多记录的查询计划（估计）就是您在 EXPLAIN 中看到的。