SQL SELECT speed int vs varchar

Question

I'm in the process of creating a table and it made me wonder.

If I store, say cars that has a make (fx BMW, Audi ect.), will it make any difference on the query speed if I store the make as an int or varchar.

So is

SELECT * FROM table WHERE make = 5 AND ...;

Faster/slower than

SELECT * FROM table WHERE make = 'audi' AND ...;

or will the speed be more or less the same?

Answer 1

If you turn on indexing on either of the fields, it will be faster. As for your question, i think intis faster than varchar.

Answer 2

In general the int will be faster. The longer is the varchar the slower it gets

Answer 3

Hint: If the possible values for the field make will never (or rarely) change, you can use ENUM as a compromise. It combines good speed with good readability.

Answer 4

Int comparisons are faster than varchar comparisons, for the simple fact that ints take up much less space than varchars.

This holds true both for unindexed and indexed access. The fastest way to go is an indexed int column.

---

As I see you've tagged the question postgreql, you might be interested in the space usage of different date types:

• int fields occupy between 2 and 8 bytes, with 4 being usually more than enough ( -2147483648 to +2147483647 )
• character types occupy 4 bytes plus the actual strings.

Answer 5

It will be a bit faster using an int instead of a varchar. More important for speed is to have an index on the field that the query can use to find the records.

There is another reason to use an int, and that is to normalise the database. Instead of having the text 'Mercedes-Benz' stored thousands of times in the table, you should store it's id and have the brand name stored once in a separate table.

Answer 6

Index or not, int is a lot faster (the longer the varchar, the slower it gets).

Another reason: index on varchar field will be much larger than on int. For larger tables it may mean hundreds of megabytes (and thousands of pages). That makes the performance much worse as reading the index alone requires many disk reads.