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: 201

Filed under:
|
|

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

Related posts about mysql

Related posts about indexing