Re: MySQL not using indexes

[Rick James]

> Is a well-indexed query on a table of 72M rows expected to run at around 1 - 2 minutes?
It depends. Perhaps you now see that it might take less than a second, or could take many minutes. It depends on the combination of the index and the query.

Most indexes are structured in "BTrees". This provide very efficient lookup of a single row by the key being indexed. You can expect the query to take less than a second, regardless of table size.

BTrees also provide for very efficient "range" queries, like
SELECT * FROM tbl WHERE ts BETWEEN ... AND ...
This can fetch dozens to thousands of rows per second, regardless of table size.

Neither of those examples will register as "Using index". "Using index" really means "Using _only_ an index". But, if you have millions of rows, and your query has to scan the entire index (which also has millions of 'rows'), then this "index scan" could take a long time. Still, it is likely to be faster than scanning the entire dataset.

IN (...) does lots of single-row lookups.

name LIKE "Darr%" (find all names starting with Darr) is also a 'range' query. It starts at the first Darr, and scans sequentially until it has done all the `name` values starting with Darr.

When you don't have a useful index, it has to do a "table scan". This means reading every row.

name LIKE "%ell" has to do an index scan or table scan because the order of the index is useless without having a starting letter.

A table scan will be indicated by "ALL" in Explain's 'type'.
An index scan will say "index".
A range scan will say "range".

Note: If the optimizer decides that the 'range' is too big a percentage of the table, it will punt and turn it into a table scan.