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


  • QA Engineer

    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?



  • 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 "". 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: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

    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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2