1

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?

5
  • 2
    In general with your sample, the {field} could be indexed for better performance. Beyond that, however, your generic code does not provide much information to reach a decision.
    – RLF
    Jan 10 '17 at 13:48
  • Ah okay, thanks for the response! What information do you need? I will edit my question anytime. Jan 10 '17 at 13:52
  • 1
    Agreed with RLF, the fields that are passed to these queries would be obvious starts, but if that's a large number (or all fields) then that's not practical. I'd say, look at performance. Look at the most used queries, I'm not a MySQL dev, but in MSSql you can review system tables to tell you which queries are run the most often and which ones are the most expensive. Then you can focus on those. Note, that if you index just the field(s) in your where clause you might still need to add columns for those in your select.
    – paulbarbin
    Jan 10 '17 at 14:06
  • Thanks for the response, mostly, the field is only one field, only #4 and #6 have multiple field: field and field2. And what do you mean by Note, that if you index just the field(s) in your where clause you might still need to add columns for those in your select., I am not that good in english so I do not understand what you meant in your last sentence. Jan 10 '17 at 14:13
  • #2 is the most used query in my queries. Jan 10 '17 at 14:18
2

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.
10
  • Thank you, this is really informative, I am currently reading your blog. Jan 11 '17 at 2:02
  • I added more specifics on your most popular case.
    – Rick James
    Jan 11 '17 at 2:15
  • 1
    INSERT, etc, automatically update all the indexes for the table; no manual action is needed. (It may be necessary for some other database, not MySQL.)
    – Rick James
    Jan 11 '17 at 2:40
  • 1
    Learn the mysql commandline tool. ALTER TABLE foo ADD INDEX(a,b); Or you can do that from the phpadmin window that lets you type arbitrary commands.
    – Rick James
    Jan 11 '17 at 2:41
  • 1
    <= or <, not =.
    – Rick James
    Jan 11 '17 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.