Method 1 is good but you can still use a function to generate the ids in the required format instead of using PHP for better speeds since everything will be done right inside the database. It's true u should avoid sub_queries since MySQL doesn't handle them well. MY OPINION: you can also use a join and still achieve the same. On Wed, Aug 29, 2012 at 5:54 PM, William Muriithi < william.muriithi@gmail.com> wrote:

...

*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

...