Need to index column in AND statement?

I have to do a SELECT on a table like this:

• id
• username
• speed
• is_running

The statement is like:

SELECT * 
  FROM mytable 
 WHERE usernam开发者_运维问答e = 'foo' 
   AND is_running = 1

I have an index on "username". If I'm running the above statement, do I need to also index "is_running" for best performance? Or does only the first column of the select make a difference? I'm using mysql 5.0.

---

It depends on the type of data you're storing. If it's bool, you may not see a gain from an index on that column alone. You may want to try to add a composite index on the two columns:

ALTER TABLE mytable ADD INDEX `IDX_USERNAME_IS_RUNNING` ( `username` , `is_running` );

---

It will ultimately depend on the amount of data in the table as to if you require the index. In many cases, the engine might just do a table scan and omit your index all together if it thinks that is faster. Do you have 100 users, or 100,000 users?

On a bit/bool column you are not going to utilize a ton of storage space for the index, so it probably won't hurt unless you have a really high insertion rate.

---

If you have a table with 1 million users and only 1 or 2 is running at any one time - sure, index by is_running and it will give you fantastic performance. This specific use case would do best to have 2 indexes individually on columns - username, isrunning. The reason for 2 indexes is if you are asking for is_running=0, in which case it uses the username index instead.

Otherwise, there is 0% chance of a composite index on (username, isrunning) helping anything. Stick to a single index on username.

Finally, do you really need to whole record? Select *? In some scenarios close to the tipping point (when MySQL thinks the index+lookups becomes less efficient than a straight scan), you can make this query run faster than the original. Have an index on (username,id)

SELECT mytable.*
FROM (
SELECT id 
  FROM mytable 
 WHERE username = 'foo' 
   AND is_running = 1
) X
INNER JOIN mytable on mytable.id = X.id