3

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.

Brett
  • 19,449
  • 54
  • 157
  • 290

3 Answers3

7

If I understand your question, you know that the MATCH AGAINST uses your FULLTEXT index and your wondering how MySQL goes about applying the rest of the WHERE clause (ie. does it do a tablescan or an indexed lookup).

Here's what I'm assuming about your table: it has a PRIMARY KEY on some id column and the FULLTEXT index.

So first off, MySQL will never use the FULLTEXT index for the city/state WHERE clause. Why? Because FULLTEXT indexes only apply with MATCH AGAINST. See here in the paragraph after the first set of bullets (not the Table of Contents bullets).

EDIT: In your case, assuming your table doesn't only have like 10 rows, MySQL will apply the FULLTEXT index for your MATCH AGAINST, then do a tablescan on those results to apply the city/state WHERE.

So what if you add a BTREE index onto city and state?

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

Well MySQL can only use one index for this query since it's a simple select. It will either use the FULLTEXT or the BTREE. Note that when I say one index, I mean one index definition, not one column in a multi-part index. Anwway, this then begs the question which one does it use?

That depends on the table analysis. MySQL will attempt to estimate (based on table stats from the last OPTIMIZE TABLE) which index will prune the most records. If the city/state WHERE gets you down to 10 records while the MATCH AGAINST only gets you down to 100, then MySQL will use the city__state index first for the city/state WHERE and then do a tablescan for the MATCH AGAINST.

On the other hand, if the MATCH_AGAINST gets you down to 10 records while the city/state WHERE gets you down to only a 1000, then MySQL will apply the FULLTEXT index first and tablescan for city and state.

The bottom line is the cardinality of your index. Essentially, how unique are the values that will go into your index? If every record in your table has city set to Oakland, then it's not a very unique key and so having city = 'Oakland' doesn't really reduce the number of records all that much for you. In that case, we say your city__state index has a low cardinality.

Consequently if 90% of the words in your FULLTEXT index are "John", then that doesn't really help you much either for the exact same reasons.

If you can afford the space and the UPDATE/DELETE/INSERT overhead, I would recommend adding the BTREE index and letting MySQL decide which index he wants to use. In my experience, he usually does a very good job of picking the right one.

I hope that answers your question.

EDIT: On a side note, making sure you pick the right size for your BTREE index (in my example I picked the first 10 char in city). This obviously makes a huge impact to cardinality. If you picked city(1), then obviously you'll get a lower cardinality then if you did city(10).

EDIT2: MySQL's query plan (estimation) for which index prunes the most records is what you see in EXPLAIN.

tazer84
  • 1,743
  • 12
  • 11
1

I think you can easily determine which index gets used by using EXPLAIN on your query. Please check the accepted answer for this question, which provides some good resources on how to interpret the output of EXPLAIN.

How does MySQL now find the matching rows for these since it can't use two indexes

Yes it can: Can MySQL use multiple indexes for a single query? Also, you should read the documentation: How MySQL Uses Indexes

Community
  • 1
  • 1
Mihai Todor
  • 8,014
  • 9
  • 49
  • 86
  • Thanks for the answer, but not really the type of info I'm looking for; I can see it uses the `fulltext` index in my case, but where does this leave the rest of the query with regards to indexes? – Brett Sep 18 '12 at 09:21
  • I'm afraid I don't understand what you're asking. The MySQL interpreter can choose which indexes to use, based on certain rules. The `EXPLAIN` query should provide information on all the indexes that get taken into account. – Mihai Todor Sep 18 '12 at 12:02
  • Mihai, +1. @Brett, please post the table structure (SHOW CREATE TABLE), and post the result of EXPLAIN on your query, so we can see it too ... – Marc Alff Sep 18 '12 at 14:11
  • @MihaiTodor I have edited my question and elaborated further. Posting the results of an explain likely wouldn't help, but will if you still would like to see it. – Brett Sep 18 '12 at 18:24
  • @Brett Please see the edit I made to my answer. I hope this clarifies things. If you want tips regarding optimizations, you'll need to share with us the database structure of the tables involved in the query and also the output of `EXPLAIN` (but maybe you should do it in a new question, since it won't get much attention here). – Mihai Todor Sep 18 '12 at 20:49
  • @MihaiTodor Thanks. I read that other SO question you linked me to and it seemed to indicate MySQL can only utilize multiple indexes on the same table in newer versions of MySQL? – Brett Sep 19 '12 at 11:58
  • @Brett Yes, it seems so, but I'm not sure which version of MySQL introduced this feature. Perhaps you should ask this on http://dba.stackexchange.com/ because it's specialized on such matters. – Mihai Todor Sep 19 '12 at 13:13
1

I had similar task some time ago, and I have noticed that MySQL can use either FULLTEXT index or any other index/indexes in one query, but not both; I wasn't able to mix FULLTEXT with any other index. Any selection with fulltext search will work in such way:

  • select subset using FULLTEXT search
  • select records matching other criteria from that subset 'Using where'

So you can use either fulltext index or any other index (I wasn't able to use both indexes by FORCE INDEX or anything else).

I suggest trying with both using fulltext and using other index (i.e. on City and State columns) and compare the results - they may vary depending on actual content in your database.

In my case I have discovered that forcing regular (non-fulltext) index in such query produced better performance (since I had very large number of rows, about 300 000, and non-fulltext criteria matched about 1000 of them).

I was using MySQL 5.5.24

irezvin
  • 101
  • 5