3 ms·

From my database course I learnt that nothing is slow in a database until you can't fit your join operation in memory. Having to do a a join operation on varcha

From my database course I learnt that nothing is slow in a database until you can't fit your join operation in memory. Having to do a a join operation on varchar(300) is predictable. Having to do one on text is unpredictable and can slow your system to a crawl if the operation needs to be done on disk rather than in memory. FWIK the article did not talk about joins at all. I would love if someone has a good article comparing what happens when you do a join on varchar vs text. EDIT: One question remains, how is the "text" stored when doing a join. For varchar you just make an array, because all values are the same length. With "text" do you first find the longest text and use that as the default array element size? This may not seem important with in memory operations, but seeking on disk is considerably faster when all your elements have the same size.

icebraining 13y ago
For varchar you just make an array, because all values are the same length. No, they're not, that's why it's a VARCHAR and not just a CHAR. A varchar(n) field may have any length between 1 and n.
jeltz 13y ago
The memory usage of a join has many more variables than the size of the joined field (in some cases it might not matter at all as long as that field is not included in the result set) so I would not say that joining on a short string is that much more predictable than joining on a possibly long string. What matters the most is what the query actually does, what the data looks like, and what indexes you have. Joins are usually implemented in one of these three ways, and the planner can select which one to use depending on the query and table statistics: 1. Hash join: Build a hash table of all the joined fields of set 1 and then traverse the rows of set 2 looking them up in the hash table. 2. Merge join: Sort both sets of rows and merge them. With the right indexes you may not even need to sort anything, just traverse in index order. 3. Nested loop join: For every tow in set 1 find matching entries in set 2.

icebraining 13y ago

For varchar you just make an array, because all values are the same length. No, they're not, that's why it's a VARCHAR and not just a CHAR. A varchar(n) field may have any length between 1 and n.

jeltz 13y ago

The memory usage of a join has many more variables than the size of the joined field (in some cases it might not matter at all as long as that field is not included in the result set) so I would not say that joining on a short string is that much more predictable than joining on a possibly long string. What matters the most is what the query actually does, what the data looks like, and what indexes you have. Joins are usually implemented in one of these three ways, and the planner can select which one to use depending on the query and table statistics: 1. Hash join: Build a hash table of all the joined fields of set 1 and then traverse the rows of set 2 looking them up in the hash table. 2. Merge join: Sort both sets of rows and merge them. With the right indexes you may not even need to sort anything, just traverse in index order. 3. Nested loop join: For every tow in set 1 find matching entries in set 2.