Mysql – Optimal indexing for SELECT (thesql)

database-designindex-tuningMySQL

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?

Best Answer

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

Related Solutions

Mysql – optimal table design thesql with primay key and varchar value

You may find what I am about to suggest a little surprising

Add the following indexes

ALTER TABLE mytable ADD INDEX id_value_ndx (id,value);
ALTER TABLE mytable ADD INDEX value_id_ndx (value,id);

This will actually cache id and value together in the MyISAM Key Cache. That way, all queries are in memory only. Queries will not go to the table at all.

I would also propose you make a dedicated 1GB MyISAM Key Cache just for your table

STEP 01: Create a script to load the table into the dedicated key cache

cd /var/lib/mysql
echo "SET GLOBAL mykeycache.key_buffer_size = 1024 * 1024 * 1024;" > init-file.sql
echo "CACHE INDEX mytable IN mykeycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE mykeycache; >> init-file.sql

STEP 02 : Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 03 : service mysql restart

From here on, every restart of mysql will setup this private key cache for your table

Give it a Try !!!

Mysql – Concept to keep track of changes in all tables

The answer depends on what you intend to do with the data. Are you going to be doing frequent queries against it or do you only need it for the occasional lookup of what happened when, to rollback bad changes, or for regulatory reasons?

In the first case above, use the second process you descibed although I would add a column to denote the active record. I would also add a record created date. I wouild also create a view of just the active records and use that for allof my code that needs to see the current data.

In the second case above, I would have audit tables that are populated through triggers (the offective way to create audit records for any change to the db) and that also include the data of the change and the user or application that made the change as well as the old and new values.