Re:Squeeze the hell out of the system you have
by unnouinceput on Saturday August 12, 2023 @03:26AM
(#37097892)
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.
[ Parent
| on HN ]
Re:Squeeze the hell out of the system you have
by aidos on Saturday August 12, 2023 @03:57AM
(#37098056)
That seems weirdly convoluted. So you store two columns to represent the foreign key?
Re:Squeeze the hell out of the system you have
by unnouinceput on Saturday August 12, 2023 @06:55AM
(#37098893)
If 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.
Re:Squeeze the hell out of the system you have
by aidos on Saturday August 12, 2023 @07:10AM
(#37098975)
I 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.