0

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?

1 Answer 1

0
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.