Analyzing and maybe optimizing a query.

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi there,

I'm building a forum with a mysql backend. The forum has the following
- category
     |--> forum
              |--> thread
                       |--> replies

Once the user has defined a forum (id), i first need to get the name of
the forum,
the category it's in, and how many threads there are in the current
I use the query below, which works fine, *but* ( shame, shame ) i don't
really understand how/why it works.

I tried to read the mysql manual and everything, but since my native
language isn't english, and there are quite a lot of technical related
words in it, it doesn't really make sence to me.
I built this query myself, with a lot of trial & error work ...

Again, the query works fine, but i don't understand how, and i don't
know wether  or not it can be optimized ... (especially the GROUP BY
part i seize to understand)


**** Q U E R Y *****************************************


f.`title`   AS     'forum_title',
c.`title`     AS     'cat_title',
COUNT( t.`id` )     AS     'number_of_threads'
`fo_cats` c, `fo_threads` t
LEFT JOIN     `fo_forum` f
ON     f.`cat_id`     = c.`id`
WHERE     f.`id`     =     $forum_id
AND     t.`forum_id`     =     $forum_id
GROUP BY     'cat_id',      'cat_title'
ORDER BY     `cat_id` ASC
LIMIT     1");

**** E N D   O F  Q U E R Y *****************************************

Re: Analyzing and maybe optimizing a query.

frizzle wrote:

Quoted text here. Click to load it


Well, it is hard to help you if you don't understand the SQL yourself.
Especially the optimalisation is hard for beginners, so my advise would be  
to skip the speed-concerns for now and focus your thoughts on SQL itself.
When you are more confortable with SQL, you can start studying INDEXES and  
such to speed up thing.
When used wrong: indexes can actually slow down the process (select or  
insert) instead of accelerating, so first get a grip on the basics.

I would advise you study SQL a little more in a good book in your own  
language or online.

Here is a very simple tutorial that covers GROUP BY:

But maybe you better start here:

Just go through the lessons, and you will end up automatically in the GROUP  
BY part.

In general: Maybe it is better to keep learning english, because so much  
(good) technical information is written in english online.

Best of luck!

Erwin Moller

Site Timeline