What column/s do I need an index for my queries? I need to know where to put indexes in my queries. I am at lost as how to use it although I am searching about this for quite some time now.

Here are my queries: (in PHP)

1) In operator, I do use &gt; and =. I use = often.

SELECT {columns} FROM {table}
      WHERE {field} {operator} {value}

2) This query is for pagination, limit is always equal to 10.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
  ORDER BY date DESC LIMIT {start}, {limit}


3) This query is for checking the sent and received emails to and from other users.

SELECT {columns} FROM {table} alias1
  WHERE (alias1.{field} = {value} or alias1.{field2} = {value})
    AND EXISTS (SELECT max(alias2.date) alias_date FROM {table} alias2
       WHERE alias1.{field} = alias2.{field}
         and alias1.{field2} = alias2.{field2}
       GROUP BY {field}, {field2}
       HAVING alias1.date = alias_date)
       ORDER BY alias1.date DESC


4) A query using IN.

SELECT {columns} FROM {table}
  WHERE {field} IN ({in_data})
  ORDER BY FIELD ({field}, {in_data})


5) Query using ASC. Data in IN are numeric.

SELECT {columns} FROM {table}
  WHERE {field} IN ('in1', 'in2')
  ORDER BY date ASC


6) Query using AND.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
    AND {field2} {operator2} {value2}


7) Query with DESC.

SELECT {columns} FROM {table}
  WHERE {field} {operator} {value}
  ORDER BY date DESC


In where column/s do I need to use index? The important column//s that in need of index? I need some help.

Note

The {field} and {field2} are only one field, e.g. WHERE {lastname} = {value}.

Update

I posted a more detailed query from my codes and how to implement index based on how I understand it. Here is the link: Are the index here will suffice to make the query faster?
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.