What column/s do I need an index for my queries?
Most of your answers are here.  But here are some notes:


= is easier to index than &gt; (or other inequalities).  For that reason, you list of cases is much amplified.
Using OFFSET (under the guise of LIMIT {start}. ...) requires scanning all the skipped rows.  So, see if you can "remember where you left off" as in here.
Please use fake names instead of {...}; even a, b would be easier to read and parse.
WHERE a op 3 -- usually can use INDEX(a)
WHERE a = 3 ORDER BY b -- use INDEX(a,b), but
WHERE a &gt; 3 ORDER BY b -- won't get past the "range"; that is, INDEX(a,b) will work, but the b part will not be used.
I don't think HAVING ever factors into indexing.
IN ( constants ) is somewhere between = and &gt; for index-ability:
WHERE a IN (constants) AND b &gt; 8 might use both parts of INDEX(a,b), but
WHERE a IN (constants) ORDER BY b cannot use both parts of INDEX(a,b)
If {columns} is a short enough list, then consider a "covering" index.  (See my first link.)
ORDER BY:  ASC vs DESC usually does not matter; but
ORDER BY a ASC, b DESC cannot use INDEX(..., a, b) because the directions are different.  (Version 8.0 has a solution; I assume you are not there yet.)
OR is rarely optimizable -- that is it usually defies index usage.
Your case #6 has 4 sub-cases for operators; see my link.  Hint: The order of ANDs does not matter, but the order of columns in a composite index does.


Etc, etc.

One more thing... If you have most of those cases hitting the same table, it becomes tricky to devise the optimal set of indexes.  Note


INDEX(a) is redundant if you have INDEX(a,b)
There is a limit of 64 indexes on a table, but it is unwise to have more than, say, a dozen.


If you want more specifics, (and my blog does not suffice), please provide specific schema and queries.

"Case 2 is most popular"

If id is the PRIMARY KEY and if it is in date order:

WHERE a = const
  AND id &lt;= $left_off
ORDER BY id DESC
LIMIT 11

INDEX(a, id)

WHERE a &gt; const
  AND id &lt;= $left_off
ORDER BY id DESC
LIMIT 11

(PK suffices)


Notes:


By switching from date to id (if possible), the "left off" logic becomes much simpler.
LIMIT 11 - 10 to show, 1 to see if there is any more.  (This makes the UI more friendly, rather than linking to an empty next page.)
See the second link for more discussion.