0

I want to rewrite my MySQL query so that the last table join doesn't do a full table scan unless necessary. table1.csv is a varchar field containing comma separated IDs that relate to table2's join_id column. I only want to call FIND_IN_SET which does a full table scan when table2 isn't joined to table1 in the first join and when table1's csv column is not an empty string.

SELECT table1.id, table1.csv, GROUP_CONCAT(`unlocked`.join_id)
AS `list` FROM table1 LEFT JOIN table2 ON table1.id=table2.join_id
LEFT JOIN table2 `unlocked` ON table2.join_id IS NULL AND table1.csv<>'' AND
FIND_IN_SET(`unlocked`.join_id, table1.csv) WHERE table1.id IN ([comma separated values])
GROUP BY table1.id
PHPguru
  • 491
  • 4
  • 5
  • You might want to consider asking over at http://dba.stackexchange.com/ This seems to be a subject in their topic range: http://dba.stackexchange.com/help/on-topic, seems to fall under "Advanced Querying including window-functions, dynamic-sql, and query-performance" – Claies Mar 29 '15 at 01:13
  • Thanks for the suggestion. I did that. – PHPguru Mar 29 '15 at 03:34

0 Answers0