Are there real-world scenarios where to perform a FTS is preferable to scanning an index with very low cardinality? Why would MySQL do so?

Doesn't an index scan always involve reading less data (pages)?

  • 1
    do you mean the server thought the index stats were screwed up, so it ditched their use, you did the equivalent of analyze table, and it used it and non-FTS – Drew Jun 15 '16 at 17:34
  • Index with low cardinality is "bad", so if the cardinality was wrongly estimated (or really properly estimated as a column with 31 values per 30M rows has low cardinality) the optimizer had no way to know the index will be useful. Is the table MyISAM or InnoDB? – jkavalik Jun 15 '16 at 18:46
  • I've removed my specific case, because I'm mostly interested in the general case. My case was anyway on a 30M records InnoDB table (ANALYZE TABLE performed). Using an index was much faster, but MySQL doesn't use it, unless FORCE INDEX is specified. – Marcus Jun 17 '16 at 10:50

Let's imagine a large table, and the index is essentially the rows numbered. And we want every 50th row via id IN (50,100,150,200, ...). Further, let's say that there are about 100 rows per block.

If you do a table scan, it will read all the blocks. That scan will read a block, pick up 2 rows, move on to the next block, etc. By the time it finishes, it will have read each block once. If you use the index, it will bounce between the index and the data; this is slightly worse. Still, one block will yield 2 rows before moving on to the next block.

Now, let's change the example a little. Let's say that the rows are ordered by something else. Now 50 and 100 are very likely to be in different blocks, etc. Further, let's say that the table is at least twice as big as the cache.

With this case, using the index will (usually) grab one block, extract one row from it, leave the block cached. But, before the other row in that block is requested, the block may get bumped from cache. That is, all the blocks will be read twice. The table scan would be twice as fast because it would need read the blocks only once, essentially as in the first case.

(Using UUIDs, GUIDs, MD5s, or SHA1s as the PRIMARY KEY is a simple way to create this problem.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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