Use composite index or index merge to support searching variable columns?

Question

I'm trying to understand how to best add indexes to the following table:

CREATE TABLE ideas (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 idea TEXT NOT NULL,
 age_range VARCHAR(30) NULL,
 county VARCHAR(30) NULL,
 category VARCHAR(30) NULL,
 subcategory VARCHAR(30) NULL,
 language_choice VARCHAR(30) NULL
 entry_date DATETIME,
);

I've already added a fulltext index on idea:

ALTER TABLE ideas ADD FULLTEXT (idea);

I need the table to support searches across different combinations of columns, e.g.

WHERE language_choice = 'English' AND county IN ('foo', 'bar') AND MATCH(`idea`) AGAINST('baz')
WHERE language_choice = 'English' AND county = 'foo' AND age_range = 'bar'
WHERE language_choice = 'English' AND category = 'foo' AND age_range = 'bar' AND MATCH(`idea`) AGAINST('baz')

... basically, any combination of the VARCHAR columns and the idea column, with possible multiple search values specified for each.

The language_choice column contains the fewest distinct values, and will be specified in all queries.

I think my options here are to either create a composite index, or create multiple single indexes on the VARCHAR columns and let mysql merge them. Is this correct? What's recommended? Is there another option?

Answer 1

FULLTEXT(category, country, idea)
SELECT ... WHERE MATCH(category, country, idea) AGAINST("string")

That is, lump all string columns together in a single fulltext index and apply the search against that.

Fulltext has limitations on minimum word length, "stop" words, punctuation, etc. Notice that I did not include "age_range" for fear that that involves numbers and/or "<" and ">". If, instead, "age_range" is "child/teen/adult/senior", then it could be treated as a string and included in the Fulltext technique.

Now, to address your ideas:

WHERE language_choice = 'English'
 AND county IN ('foo', 'bar')
 AND MATCH(`idea`) AGAINST('baz')

The FULLTEXT search will be done first; indexes on the other columns won't matter.

WHERE language_choice = 'English'
 AND county = 'foo'
 AND age_range = 'bar'

That would benefit from this composite index; the order of the columns won't matter (since all the tests are simply "="): INDEX(language_choice, country, age_range)

WHERE language_choice = 'English'
 AND category = 'foo'
 AND age_range = 'bar'
 AND MATCH(`idea`) AGAINST('baz')

Again, the FULLTEXT takes precedence.

More AND cases: Index Cookbook

Use EXPLAIN SELECT ... to see what is going on. I would be excited to see any cases you turn up where "Index merge...Intersect" is used. Especially if Fulltext is included. When "Intersect" (without Fulltext), it almost certainly indicates that a composite index would be better.

OR and "Index merge...Union" is a different kettle of fish.