2 Solution. which is best?

Selection question & number of answers from 2 tables...

[smagdy]

Hello,

I just need a little advice/help with this...

I have table "question" & table "answers"

each question has several answers...

I want to select all/some questions and same time get the number of answers for each question..

So my 2 solutions are either:
1. Use maybe complicated "Join"
2. Each time a question is answered I increment answers_count field in table "question" by 1

For me the 2nd solution is much easier and maybe faster in selecting wise, but I also hear that its not a good idea to save something in a table that you can calculate!

So whats your suggestions and if u suggest number 1 then please give me fast ex. on the join how to do it...

Thanks in advance

[siMKin]

definitely the 1st one.
the query will look like this:

SELECT
Table1.ID, Table1.Other, COUNT(Table2.ID) AS total
FROM Table1
LEFT JOIN Table2 ON Table2.T1ID=Table1.ID
GROUP BY Table1.ID, Table1.Other

just make sure you have set the right indexes and this should run very smoothly

[smagdy]

Hello,

Thanks a lot, i just applied it and it worked fine.. but ive 2 more questions...

1- Where should i apply the INDEXES? is it on (Table2.ID)
2- Why is this JOIN method better than incrementing the value so i just pick it up? I mean it looks like its faster than joining between tables!

Thanks in advance

[the_nerd]

Why is this JOIN method better than incrementing the value so i just pick it up

It's not always better. If you can do it, you should try to avoid "duplicate content" AKA redundant data in your database. And in your rather easy example I'd definitely stick to it.

But sometimes you have to actively create these redundancies (I hope you have this word or something close in English) . For example look at ebay: they don't pull the product lists from the real database but from some kind of "cache". Only when you click on an item you get to see real-time data. Or imagine you have to pull your product data from 24 different database tables. In this case it could be wise to store "copies" of data in well-defined places.

[smagdy]

What about the INDEXES... where should i apply them?

Thanks

[siMKin]

You should apply indexes on the columns that you often use to lookup data. So, for example, columns with IDs are almost always columns that you will want to index