INT vs VARCHAR indexes

Not sure why, but I feel like an indexed integer field would perform faster and with less resources than an indexed varchar field. Is that true or is an indexed field and indexed field regardless of data type and therefore in the real world they can be used interchangably. Thanks

this is still just a bit too general of a question for a good answer :slight_smile:

let’s first of all consider what indexing does for you – it makes search queries very efficient

but a search query only works when you are searching for something specific

so you would be supplying to the query a value – either an integer value or a string value

the missing context here is where that value will come from

let’s suppose there is a web page with some way of choosing a value – this could be a dropdown, a link with a value coded into the url, something like that

still with me? i hope i’m being a little more specific here than your question, because without context the issue is hard to talk about

anyhow, the point is, if you were going to do a search like WHERE id=42 or WHERE name=‘foobar’, an index on either column would ensure that the query is efficient

but how did those values get into the dropdown, or into the url? supposedly, they were put there by your app which obtained the values from the table

furthermore, let me point out that if, as you suggested, the table has both an integer column and a string column, from the point of view of the table design, and assuming both are unique (you can see how talking without specific context leads to a lot of assuming and supposing), then one of those columns is redundant

how’m i doin’?

:slight_smile:

It is in the context of using RewriteRules and bookmarking pages. I can use either the auto-generated key number or the name of the company in the generated url. The name looks better in the url so that would be the preference and I suppose since it is only a single record query, you’re right, how much of a difference can it make. So in that case it may be a moot point.

But in a best practices case, is there a general rule for that? Assuming I have two columns in the same table, one INT and one VARCHAR, should I use the INT over the VARCHAR or is it just a coin toss?

Oh, and I have your book already. Has helped me many times. :slight_smile:

context for this question?

i mean, you can’t just switch an integer column with a string column, it has to be done in the context of a particular table

an index on a string column might be slower, sure, but so what? if you were going to search a table of employees for a particular name, there’s not much point in searching on the employee number, even though the index on the employee number might be faster

a second consideration is how much – if you cannot measure the difference in performance, does the difference really matter?

Great points. And in thinking about it, the name is not unique so it does me no good to search on the name alone. I’m starting to think maybe I’m overthinking this!

So to summarize, the query on a unique indexed field is always the best, whether that be a string or an INT and you’re right, if you have two unique columns in a table, your data is redundant.

Although, and there’s always one of those, sometimes there may be a unique field but it may be changeable, such as email, so I always prefer a unique auto-increment key to most informational tables. Especially for relating tables.

In any event, thanks so much for the input. It helps tremendously.