Could use some opinions on indexes

Hey guys,

Finding a more experienced group of developers is a tough challenge,
so I'm posting this here. It's slightly Rails-related, but mostly a
database issue.

So I'm using AuthLogic for an app I'm building. Should I be worried
about putting indexes on fields like crypted_password, password_salt,
persistence_token, single_access_token, perishable_token, etc.?

My general understanding on indexes (I'm more of a hacker than a
"developer", so bear that in mind) is that they're essentially a "map"
of what data is where so the database can do its select queries
faster. As far as I know, AuthLogic isn't going to be doing any
select queries based off the fields above (my thinking is, look up by
uuid [e-mail for example], then compare stuff against that record),
but I definitely could be wrong.

Any thoughts here guys? It's appreciated - thank you.

PS - does anyone know a good/accurate way to predict the size of a
field index as it reaches N number of rows? For example, let's assume
that even though my 'email' field is a varchar(255), that all 255
characters are used; if I have 5 users this is laughable, but 500,000
sure as hell isn't. How can I accurately predict the disk space size
of an index (basically trying to get an idea of how big a database I
need to buy from Heroku) once the number of rows in that table hits
"N"?

Hey guys,

Finding a more experienced group of developers is a tough challenge,
so I'm posting this here. It's slightly Rails-related, but mostly a
database issue.

So I'm using AuthLogic for an app I'm building. Should I be worried
about putting indexes on fields like crypted_password, password_salt,
persistence_token, single_access_token, perishable_token, etc.?

My general understanding on indexes (I'm more of a hacker than a
"developer", so bear that in mind) is that they're essentially a "map"
of what data is where so the database can do its select queries
faster. As far as I know, AuthLogic isn't going to be doing any
select queries based off the fields above (my thinking is, look up by
uuid [e-mail for example], then compare stuff against that record),
but I definitely could be wrong.

Any thoughts here guys? It's appreciated - thank you.

Well one way would be to inspect your log file - you'll see those
queries being executed. databases usually have some kind of query
logging facility for logging 'bad' queries, queries that required a
full table scan would usually fit that definition. Off the top of my
head single_access_token is used for loading users in some
circumstances (might be something like password resets or something
like that)

PS - does anyone know a good/accurate way to predict the size of a
field index as it reaches N number of rows? For example, let's assume
that even though my 'email' field is a varchar(255), that all 255
characters are used; if I have 5 users this is laughable, but 500,000
sure as hell isn't. How can I accurately predict the disk space size
of an index (basically trying to get an idea of how big a database I
need to buy from Heroku) once the number of rows in that table hits

I would assume that index/data size would scale linearly with the
number of rows in the table, but check your db's documentation

Fred