Analyzing and maybe optimizing a query.

[frizzle]

Hi there,

I'm building a forum with a mysql backend. The forum has the following
structure:
- 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
forum.
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)


Frizzle.

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

mysql_query("

SELECT
f.`title` AS 'forum_title',
c.`title` AS 'cat_title',
COUNT( t.`id` ) AS 'number_of_thre ads'
FROM
`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 *************** *************** ***********

[Erwin Moller]

Re: Analyzing and maybe optimizing a query.

frizzle wrote:
[color=blue]
> Hi there,
>
> I'm building a forum with a mysql backend. The forum has the following
> structure:
> - 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
> forum.
> 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)
>
>
> Frizzle.
>
> **** Q U E R Y *************** *************** ***********
>
> mysql_query("
>
> SELECT
> f.`title` AS 'forum_title',
> c.`title` AS 'cat_title',
> COUNT( t.`id` ) AS 'number_of_thre ads'
> FROM
> `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 *************** *************** ***********[/color]


Hi,

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:

http://www.w3schools.com/sql/sql_groupby.asp

But maybe you better start here:

http://www.w3schools.com/sql/default.asp

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!

Regards,
Erwin Moller