in reply to OT - SQL choosing a schema for index tables

Correct, this is way OT. You should do some reading elsewhere on database design; your question is fundamental. (Look into JOIN, you rarely need nested SELECTs for the type of work you described.)

The way forward always starts with a minimal test.

In Section Seekers of Perl Wisdom