3 ms·
If the tables involved in the join are of 100M+ records what I do when the joins use varchar columns to improve the performance is to use an additional integer
by unnouinceput 3y ago
If the tables involved in the join are of 100M+ records what I do when the joins use varchar columns to improve the performance is to use an additional integer column of the varchar one that is a CRC of it (or hash if you prefer that) and use the integer one instead in the join.
- aidos 3y agoThat seems weirdly convoluted. So you store two columns to represent the foreign key?
- unnouinceput 3y agoIf you use a varchar as FK then you're definitely doing something wrong from beginning. OP was talking about getting the phone number under certain conditions, and a phone number column is a varchar.
- aidos 3y agoI don’t think they ever did a lookup on phone number and even if you needed to, you would just index it and it’d be fast. You can even use things like tri-gram indexes to give you super fast partial phone number matching.