MySQL: How to make an Index Scan run faster than a Full Table Scan?

I read from MySQL Performance Blog that depending on the selectivity of a query, a full table scan can be faster than an index scan. Bearing such knowledge in mind, I experimented with a query having 12 WHERE crite开发者_Python百科ria and one HAVING criterion on a table with 5 Million rows as per my previous post. I observed that the full table scan is still much faster (7.7sec) than an index scan with a four-column index (160sec) that has 3% selectivity (161341/5000000).

My question is: "Why is an index scan having 3% selectivity still 20 times slower than a full table scan? Is there a way to make the index scan faster?"

an index scan with a four-column index

The order of the columns in the index makes a difference to the performance. Put the most selective column first in the index. And it also depends on your query. A WHERE clause with 'x LIKE '%foo%' will notuse the index effectively, but 'x LIKE 'foo%' will be more effective.