*Real life:* A questionnaire is grouped into several sections (e.g. personal details, contacts, education, work experience etc), each section then has multiple questions.
*Current implementation:* I have 3 tables; questionnaires (columns: id, title, ...), sections (id, questionnaire_id, ...) and questions (id, section_id, ...). The columns questionnaire_id and section_id are indexed, and all "id" columns are primary keys, auto increment.
*The need:* I need to get all questions that belong to a specific questionnaire. Lets assume the id of this questionnaire is 1.
*Method 1:* 1. Run the query "SELECT id FROM sections WHERE questionnaire_id=1" 2. Do some PHP code to retrieve the ids and convert them into CSV format, e.g. 1, 2, 3 3. Run the query "SELECT id, title, ... FROM questions WHERE section_id IN (1,2,3)"
*Method 2:* 1. Run the query "SELECT id, title, ... FROM questions WHERE section_id IN (SELECT id FROM sections WHERE questionnaire_id=1)"
*Question:* Which is the better method in terms of speed and scalability?
Would go with the first one. Sub queries are better avoided. But test which is faster by running them through explain William