What column/s do I need an index for my queries?

Question

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 > 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?

Answer 1

Most of your answers are here. But here are some notes:

• = is easier to index than > (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 > 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 > for index-ability:
• WHERE a IN (constants) AND b > 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 <= $left_off
ORDER BY id DESC
LIMIT 11

INDEX(a, id)

WHERE a > const
  AND id <= $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.