Performance of string comparison vs int join in SQL

sql performance join foreign-keys varchar
RobertMGlynn picture RobertMGlynn · Sep 14, 2012 · Viewed 19.7k times · Source

It's accepted that searching a table on an int column is faster than on a string column (say varchar).

However, if I have a Shirt table with a Color column, would it be more performant to create a Color table with the primary key on that table being the foreign key on the Shirt table? Would the join negate the performance advantage of having the value in the Color column on Shirt being an int instead of a string value such as "Green" when searching for green Shirts?

Answer

Mark Wilkins picture Mark Wilkins · Sep 14, 2012

If I understand correctly, you are asking which of these two queries would be faster:

SELECT * FROM shirt where color = 'Green'

vs

SELECT shirt.* FROM shirt s INNER JOIN colors c 
       ON s.colorid = c.colorid 
       WHERE c.color = 'Green'

It depends a little bit on the database (well ... maybe a lot depending on if it optimizes correctly, which most if not all should), but the lookup in the color table should be negligible and then the remaining execution could use the integer lookup value and should be faster. The bulk of the processing ultimately would be equivalent to SELECT * from shirt WHERE colorid=N. However, I suspect that you would not notice a difference in speed unless the table was quite large. The decision should probably be based on which design makes the most sense (probably the normalized one).

Related questions

SQL Joins Vs SQL Subqueries (Performance)?

I wish to know if I have a join query something like this - Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id and a subquery something like this - Select E.Id,…

Read More
Difference between Oracle's plus (+) notation and ansi JOIN notation?

What's the difference between using oracle's plus notation (+) over the ansi standard join notation? Is there a difference in performance? Is the plus notation deprecated?

Read More
Subqueries vs joins

I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like: WHERE id IN (SELECT id FROM ...) The refactored query runs about 100x faster. (~50 seconds to ~0.3) I expected …

Read More