Fulltext and composite indexes and how they affect the query
Posted
by
Brett
on Stack Overflow
See other posts from Stack Overflow
or by Brett
Published on 2012-09-14T15:02:48Z
Indexed on
2012/09/21
3:38 UTC
Read the original article
Hit count: 224
Just say I had a query as below..
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;
..and I had a fulltext
index as name,subcategory,category,tag1
and a composite
index as city,state
; is this good enough for this query? Just wondering if something extra is needed when mixing additional AND
's when making use of the fulltext index with the MATCH/AGAINST.
Edit: What I am trying to understand is, what happens with the additional columns that are within the query but are not indexed in the chosen index (the fulltext index), the above example being city
and state
. How does MySQL now find the matching rows for these since it can't use two indexes (or can it?) - so, basically, I'm trying to understand how MySQL goes about finding the data optimally for the columns NOT in the chosen fulltext index and if there is anything I can or should do to optimize the query.
© Stack Overflow or respective owner