This is not specific to MySQL, it is about B-tree indexes in general.
Leaving aside the implementation details, you can imagine a B-tree index as a sorted list of the indexed columns with a pointer to the table.
So if you imagine a two-column index on (num1, num2)
, it would look somewhat like this:
num1 | num2 | pointer
--------+--------+---------
1 | 1 |
1 | 3 |
1 | 42 |
1 | 42 |
1 | 28643 |
1 | 36001 |
2 | 1 |
2 | 41 |
2 | 1001 |
2 | 10000 |
5 | 2 |
5 | 123 |
5 | 2054 |
...
Now an index can be used efficiently if all the columns you are looking for are right next to each other in the index. (There is what MySQL calls a loose index scan with which an index can be used differently, but that does not apply to the discussion at hand.)
If your query contains
WHERE num1 = 2 AND num2 > 10
it is obvious that the index can be used efficiently: all these index entries are right next to each other.
If your query contains
WHERE num1 <= 5 AND num2 > 1000
then the index can be used for the first condition, because all these rows are right next to each other), but among these rows, the ones with num2 > 1000
are all over the place, so this second condition can only be used as a filter to remove rows found by the index scan.