SQL SELECT speed int vs varchar

sql performance postgresql select
googletorp picture googletorp · Feb 27, 2010 · Viewed 83k times · Source

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

Robert Munteanu picture Robert Munteanu · Feb 27, 2010

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:

Related questions

Best way to select random rows PostgreSQL

I want a random selection of rows in PostgreSQL, I tried this: select * from table where random() < 0.01; But some other recommend this: select * from table order by random() limit 1000; I have a very large table with 500 Million rows, I …

Read More
SQL, Postgres OIDs, What are they and why are they useful?

I am looking at some PostgreSQL table creation and I stumbled upon this: CREATE TABLE ( ... ) WITH ( OIDS = FALSE ); I read the documentation provided by postgres and I know the concept of object identifier from OOP but still I do not …

Read More
How big is too big for a PostgreSQL table?

I'm working on the design for a RoR project for my company, and our development team has already run into a bit of a debate about the design, specifically the database. We have a model called Message that needs to …

Read More