Question 113338 in Mysql Question posted in
The official MySQL documentation can be found here.

Should I create separate MySQL indexes for url_title vs url_title, url_description, url_keywords?

Using MySQL 5.7, I have a table of urls containing url_title, url_description, url_keywords

Sometimes I just need to look in url_title, but sometimes look for something in all columns.

Is it better to just create one index containing all 3 columns or create a separate index for url_title alone and another index containing all 3 columns ?

e.g Will it search for url_title slower in the 3 columns index vs single column ?

Or can MySQL only search/read in given column even if index would contain 3 columns ?

Later edit: this is a sample query but I do have other less important variations:

SELECT  *
    FROM  urls
    WHERE  match(url_title, url_description,
                 url_keywords, url_paragraphs)
           against('red boots' IN BOOLEAN MODE)
    LIMIT  500
Tags:

2

Answers


  1. 0

    Update: You didn’t mention in your original post that you were talking about fulltext indexes, not conventional B-tree indexes.

    Fulltext indexes are a different type. You must specify ALL the columns of the fulltext index in your MATCH() clause. No fewer, and no more, and they must be in the same order as they appear in the index definition.

    If you want to do a fulltext search only on a single column sometimes, then you will have to create another fulltext with that single column.

    Below is my original answer, that I wrote before you clarified that you were using a fulltext index. Perhaps it will help someone else.

    MySQL can use the index if the column(s) you search are the leftmost column(s) of that index. It can use a subset of the columns of a multi-column index.

    For example, given an index on (a, b, c), the following query uses all three columns:

    SELECT ... WHERE a = ? AND b = ? AND c = ?

    The following query uses the first column a of the index, because it’s the leftmost column.

    SELECT ... WHERE a = ?

    The following query uses the first two columns of the index, because they’re consecutive and the leftmost subset of columns.

    SELECT ... WHERE a = ? AND b = ?

    The following query uses only the first column a of the index, because the conditions don’t match consecutive columns of the index. It will use the index to narrow down the search to rows matching the a condition, but then it will have to examine each of those rows to evaluate the c condition, even though c is part of the same index.

    SELECT ... WHERE a = ? AND c = ?

    MySQL has an optimization called index condition pushdown which does a short-cut for this. It delegates to the storage engine to evaluate the c condition, knowing that c is part of the index. So it still counts as examining the row, but it make the row read a little bit less costly.

    The following query cannot use the index at all, because the conditions are not on leftmost columns of that index.

    SELECT ... WHERE b = ? AND c = ?
    Login or Signup to reply.
  2. 0

    The guidelines for FULLTEXT indexes and MATCH...AGAINST are different than for INDEX. For this:

    SELECT  *
    FROM  urls
    WHERE  match(url_title, url_description,
                 url_keywords, url_paragraphs)
           against('red boots' IN BOOLEAN MODE)
    LIMIT  500

    (and assuming ENGINE=InnoDB), you need a FULLTEXT index with all 4 columns in it.

    FULLTEXT(url_title, url_description,
         url_keywords, url_paragraphs)

    If you might also be searching, say, just url_title in another query, then you would also need FULLTEXT(url_title). (Etc)

    See if either of these would be ‘better’ for your application:

               against('+red +boots' IN BOOLEAN MODE)
           against('red boots')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search