1

What indexes would you recommend for the following select (and why)? Keep in mind there can be tens of millions of rows.

SELECT * FROM mytable WHERE md5 = ? AND created_at >= ? AND created_at <= ? LIMIT 200

Where:

  • md5 = CHAR(32)
  • created_at = DATETIME

The question is: what indexes would you add?

2 Answers 2

2

SUGGESTION #1

Based on the WHERE clause, I highly recommend a compound index

ALTER TABLE mytable ADD INDEX (md5,created_at);

That way, all parts of the WHERE clause are answered by the index.

SUGGESTION #2

Since you are imposing a limit of 200, collect the 200 keys first, then join the keys

SELECT B.* FROM
(
    SELECT id FROM mytable
    WHERE md5 = ?
    AND created_at >= ?
    AND created_at <= ? LIMIT 200
) A INNER JOIN mytable B USING (id);

I have suggested this technique before in StackOverflow

GIVE IT A TRY !!!

1
  • BTW don't worry about not having an ORDER BY clause. The order of retrieval is imposed by the index. Dec 16, 2014 at 22:05
1

Go for md5(15) because it has better cardinality and ref always beats range in performance.

You wrote LIMIT 200 without ordering clause. Just heads up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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