5

MySQL (Innodb) uses inverted indexing or forward indexing ?

From article What's the difference between an inverted index and a plain old index?

, what I understand is whenever I get record from key (like and string, int), it is inverted indexing. Taking "inverted indexing" in this way means mySQL uses inverted indexing.
But then why mySQL uses term "index" in place of "inverted index"?

Community
  • 1
  • 1
user811602
  • 1,314
  • 2
  • 17
  • 47
  • 1
    A B-tree index would be a "plain old index". A full-text index would be an inverted index. – Gordon Linoff Feb 24 '15 at 13:24
  • @GordonLinoff, thx for replying. I know that for full text search (like in solr) , inverted index is used. But I do not get any mysql document saying "forward" index. It only says "index" which can be inverted or forward. B-Tree is just algorithim which can be used in forward and inverted index both – user811602 Feb 24 '15 at 13:37
  • Inverted index is a confusing name, it just got named like that historically. The index in a relational DB v/s one in a search engine are fairly different in the way they work (the former uses a B-tree, the latter uses segments and sorting), it's not like one of them is inverted and one isn't. – Siddhartha Dec 12 '20 at 19:59

1 Answers1

4

I'm assuming InnoDB.

MySQL uses inverted indexes for its FULL TEXT indexes.

However, a standard clustered or secondary index are neither inverted or forward indexes. I don't know if there is a standard term for their architecture. Perhaps it's the plain old index or dense index. For each table record, there is one index entry.

As mentioned previously in comments, MySQL uses a B-Tree format by default.

MySQL does not yet (v5.6) support descending order indexes. E.g. when using the DESC keyword for sorting, MySQL may simply traverse the index backwards.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • Thx for answering. I haven't got any link stating "inverted" index used by MySQL. Though I believe in your statement by self arguments, it will be great help if you can provide me any reference(like links) for same . I want to read more in it. It will also help others who want read more about same. – user811602 Feb 24 '15 at 14:39