If one has a table with two columns like:
MyTable:
F_ID Name
1 ABC
1 ABC
1 DEF
2 ABC
2 ABC
2 DEF
3 DEF
3 ABC
3 DEF
Where instead of 3 names with the same foreign ID there could be hundreds.
If One wishes to search for "ABC" returning only one match per F_ID, what is the most efficient way of doing it?
One could do:
SELECT F_ID,Name FROM MyTable WHERE Name = 'ABC' GROUP BY F_ID
However, this will create a temporary table with
1 ABC
1 ABC
2 ABC
2 ABC
3 ABC
And then remove duplicates to get
1 ABC
2 ABC
3 ABC
Which doesn't scale well in a scenaro where thare are a million records with f_id = 1
Alternatively one could build up a temporary table using LIMIT 1 queries, and selecting a new row where F_ID is not in the accumulated table. This performs infinitely better than GROUP BY in the worst case scenarios, but the average case is very bad.
Any ideas?