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)?
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.)