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 *************** *************** *********** 1 1431
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_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 *************** *************** ***********
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: A Future Computer Scientist |
last post by:
A question: Is it really important to think about optimizing the native code
or optimizing it for P Code? Or does the code you write make a difference?
|
by: Virginia |
last post by:
We have a product that runs on Oracle. The version of Oracle I'm
working with is 8.1.7. I should also note that I'm relatively new to
Oracle.
I'm troubleshooting one particular database that is having performance
problems when we try to run various quireies and peform inserts or
updates.
Unfortunately this particular product "requires"...
|
by: Uros |
last post by:
Hello!
I have some trouble getting good results from my query.
here is structure
stat_views
id | integer
id_zone | integer
created | timestamp
|
by: Ryan |
last post by:
I came across a situation that I've been unable to explain and was
hoping somebody had an answer:
I had written an update query which was taking about 8 seconds to run
and considered it too slow. I copied the SQL statement from the query
and tried executing it from code which then ran in 1 second. To make
sure that I didn't miss anything,...
|
by: Joe Ross |
last post by:
(Apologies in advance if there is a better forum for asking advice on this
topic).
Our ASP.NET application occasionally starts spitting out OutOfMemory
exceptions. When this happens, the memory usage for that IIS worker process
is over 1GB. I understand in this sceneario that the virtual memory pool
can become fragmented and produce this...
| |
by: Greg Stark |
last post by:
I have a query that is taking too long when run from a larger plpgsql function
(40-50s). However when I explain analyze it under psql it runs fine (4-5s).
This is with the same parameters, and I've even tried embedding the parameters
inside a subquery to avoid letting the planner see any more info than normal.
Is there any way to ask the...
|
by: xpcer |
last post by:
hi, friends, i have an problem, like this,
i have tables, when i want to use "select" statement that include "join" sintaxt, my query will execute about 1 hour, so long.
can u tell me how to optimizing my query, may be using index, or another?
|
by: rashmigaikwad |
last post by:
Hi All,
I need help in optimizing the query mentioned below:
SELECT
SUM(CASE WHEN PROD_TYP='HBRMC' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBRMC_2p5,
SUM(CASE WHEN PROD_TYP='HBNMC' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) HBNMC_2p5,
SUM(CASE...
|
by: John Rivers |
last post by:
Hello
has anybody else noticed
I have queries that SQL 2000 optimizes correctly and they run very
fast
Yet SQL 2005 keeps using a dumb query plan and queries run very slow
The problem seems to stem from the assumption that data in a derived
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |