SQL SELECT speed int vs varchar

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.

Some rough benchmarks:

4 million records in Postgres 9.x

Table A = base table with some columnsTable B = Table A + extra column id of type bigint with random numbersTable C = Table A + extra column id of type text with random 16-char ASCII strings

Results on 8GB RAM, i7, SSD laptop:

Size on disk:                A=261MB        B=292MB        C=322MBNon-indexed by id: select count(*), select by id: 450ms same on all tablesInsert* one row per TX:       B=9ms/record        C=9ms/recordBulk insert* in single TX:    B=140usec/record    C=180usec/recordIndexed by id, select by id:  B=about 200us       C=about 200us* inserts to the table already containing 4M records

so it looks like for this setup, as long as your indexes fit in RAM, bigint vs 16-char text makes no difference in speed.

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.