Rules for indexing limited use table - MySQL

I have a table with 8 columns with mixed datatypes (int, text, varchar, etc..)

This table only has one query that operates on it (a SELECT), and within that开发者_运维知识库 query it uses 4 "AND" statements to filter the results.

My question is: what are some guidelines for indexing in this situation?

In my case, read times have a higher priority than write times, so should I index all the columns that appear in an AND clause? Should I only index the integers?

---

For just one query, make an index with all of the columns affected.

MySQL's indexes work from left to right, meaning that if you have a table with the columns:

A varchar(255)
B varchar(50)
C int(11)
D datetime

An index on KEY index (A,B,C,D) means that MySQL will look for the columns in your query, from left to right. If you include them all in your query, it will use them all but if you only include A and B, it will use these to filter down the results before applying the rest of the conditions to the remaining result set (assuming you're using AND).

However if you only include C and D, it will not use this index at all since it doesn't know what to look for in A.

---

If you take a quick look at how MySQL uses its indexes you should be able to work out the best columns to index.

---

I think you should create an index for every column, you need for your select statements. Notice, that there is a limit in mysql on a length of a field, you create an index on. The created index can not exceed 1000 bytes for MyISAM and 767 for InnoDb (thx OMG Ponies). Here you can find an discussion about assessment of the size of the index for varchars column. If you don't have writes, it should significantly boost performance of select statements.