4

I need to understand the difference between just INDEX and a FULLTEXT index.

When I try

SHOW INDEXES FOR all_schools

What I get is a table of rows of indexes. I can obviously tell the Non_Unique indexes, but how can I tell if an index is a fulltext index? Have not found anything specific to this here or elsewhere.

-- EDIT -- The accepted answer below gives me the understanding (concisely) of the difference in nature between a regular and fulltext index.

As to how to differentiate, the link given below is a great way to IDENTIFY the fulltext indexes, so I'll repeat it here: Show a tables FULLTEXT indexed columns

Community
  • 1
  • 1
Oliver Williams
  • 5,966
  • 7
  • 36
  • 78
  • 1
    Rick, I rarely say this on forums except when it's patently obvious. Your comment is not helpful. Maybe you should build a bot and randomly pepper SO questions with this exact response. That would be fun, right? I however took the time to edit this for someone in the future, and appreciate the replies below. Try explaining to them how they shouldn't have answered and make everyone more like you.. – Oliver Williams Feb 04 '16 at 01:38

1 Answers1

5

A normal index works pretty intuitively:

id    name
--    -------------------------------
 1    Smith, John
 2    Adams, John
 3    Hancock, John

The whole string in the name field is put in the index and ordered by the collating sequence (usually alphabetically).

A FULLTEXT does almost the same thing, but words are indexed. So, for the above, the fulltext index might contain:

Adams      2
Hancock    3
John       1
John       2
John       3
Smith      1

If this were the full table, then John would not be indexed for fulltext purposes because it is present in more than 50% of the records.

wallyk
  • 56,922
  • 16
  • 83
  • 148
  • Thanks, this is going to be helpful for future readers. Remaining question is, how do I tell from mysql queries whether an index is full text or regular? – Oliver Williams Feb 02 '16 at 19:12
  • @OliverWilliams: You can't tell from the query. You have to look at the table's index structure. – wallyk Feb 02 '16 at 19:57
  • that's what I'm trying to determine. My only tool I know of is `SHOW INDEXES FOR my_table` - and I can't tell from that. Am I missing something? Thanks! – Oliver Williams Feb 02 '16 at 20:42
  • 1
    @OliverWilliams: I suspect there is a better way, but one way is shown in [this answer](http://stackoverflow.com/a/4107794/198536). – wallyk Feb 02 '16 at 21:24