On 9/22/21 16:20, David G. Johnston wrote:
I'd probably turn that index into a foreign key that just ensures that every (station,channel) that appears in the data table also appears on the lookup table. Grouping and array-ifying the lookup table would be trivial. Either modify the application code or add a trigger to populate the lookup table as needed.
I fully agree with this. Adding a trigger to populate a lookup table is a standard design in situations like this. Using "DISTINCT" almost always spells trouble for the performance.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
