[SOLVED] Index

[jaymc]

I understand indexes, but Im just wondering this

 

 

Lets say I have this table

 

name - time - valid

JAMIE - 1243 - 4

 

My query is this

 

SELECT * FROM table WHERE name = "JAMIE" AND valid = "4"

 

I put an index on `name` obviously, but does it need an index on valid. I thought yes as its part of the WHERE, put assuming mysql is logic, once its found the JAMIE rows, there is no real need for an index on valid proving there is a small amount of rows?

 

Should I have an index on both or just the primary field in the WHERE clause

[fenway]

Depends what you mean by "both" -- you mean 2 separate indexes or one covering index?

 

In general, you want the index on the column with the most selectivity... sometimes that's one (e.g. a name), but if it's just two flags each match a lot of records but together limit them, it's two.

[jaymc]

Imagine 100,000 rows

 

In that pile, I have 100 rows that match my username

 

In that pile of 100, I have 10 rows WHERE valid = '3'

 

Do I need an index on the valid field

 

Id assume it only has to search through 100 rows for the valid criteria? Therefor not required as may have negative effect

[fenway]

The question is how many records does valid = 3 match for all names?

 

Furthermore, you do get some benefit from a covering index if you decide to order by these columns, or just return part of the multi-column key vs *.

[jaymc]

Ok, so lets just say from the initial where I filter out 100 rows, from a pile of 100,000 using an index for the name

 

Fine

 

Then the remaining 100 rows, I want to add an AND to the WHERE. If there is no INDEX on the next field I am quering, does it have to go through each of the 100 to check for match, where as obviously with an index it would fly straight there

 

With that said, as a rule of thumb should I apply an index on the second condition in the WHERE clause, given the example above, or is it more over head than its worth

[fenway]

I will say it again -- you want the index on the column that's the most selective... I assume that by not addressing my valid=3 question you mean to say that this would match many more records.

 

If there are just 100 rows, and valid is indeed not selective, then it's unlikely that mysql would choose to use the index anyway, since a table scan might be more efficient.

[jaymc]

Ok thanks!