By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,678 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,678 IT Pros & Developers. It's quick & easy.

Analyzing and maybe optimizing a query.

P: n/a
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_threads'
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 *****************************************

Jan 10 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
frizzle wrote:
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_threads'
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 *****************************************

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
Jan 10 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.