options for minimizing queries

Dev Articles Community Forums Sponsor:

Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

July 23rd, 2005, 10:37 PM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

options for minimizing queries

Just as a fun project, I've decided to write some forum software in PHP. Nothing as fancy as this, of course, just something to do. Anyways, something I'm wary about is querying the database too often when viewing a page. Thus, I've been trying out ways to combine information within the tables so that one query gets more of the required data.

I have a table called 'topics' and a separate one called 'posts'. I'm trying to display the replies to that topic, but without needing 2-3 queries per topic. Say I'm displaying a list of 20 topics-40 queries is ridiculous. I considered adding a column called 'replies' to the table 'topics' and everytime somebody posts a reply to that topic, 1 is added to the integer value of 'replies'. Are there any alternatives to this method besides just making a separate query. Otherwise my option is to query for the basic topic data and then query another table to get the number of rows with that particular topic id. Is there some sort of way I can make a column in 'topics' that automatically contains the number of rows in another table with the 'topics' row's id in a certain column? Or a column that basically contains the value of another query?

I'm not highly skilled with MySQL (you may have noticed, haha), so I'm open to any help with regards to any ways to condense querying. Many thanks!

July 24th, 2005, 01:32 AM

Madpawn

My beat is correct.

Join Date: Dec 2004

Posts: 339

Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 4

You just mean the number of replies, not the replies themselves, right?

Code:

SELECT
 t.foo
,t.bar
,COUNT(p.id)
FROM
 topics t
LEFT JOIN
 posts p
ON
 t.id = p.topic_id
GROUP BY 
 t.foo
,t.bar

__________________
"A pawn is the most important piece on the chessboard -- to a pawn"

July 24th, 2005, 08:31 AM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

I'm sorry, I don't understand what you're doing there. Let me explain myself better. There are forums, topics, and posts (three different tables). Topics have a column to specify which forum they belong to. So I would like to know if, when I query the forums table, I could also get the number of rows in the topics table that belong to that forum (have that forum's id in a column).

July 24th, 2005, 10:49 AM

Madpawn

My beat is correct.

Join Date: Dec 2004

Posts: 339

Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 4

The query I posted above should give that to you, though you'll have to replace the tables and column names with those that fit your db structure. What the query above illustrates is a LEFT JOIN.

You're selecting all the info you wish from the first table (to solve the problem in your second post, this will be the forums table). You're also selecting a count of the rows in your second table (which will be the topics table). Then you're specifying which tables to join. In a left join, you will pull all results from the left (first) table, and then only the results from the right (second) table that fulfill the requirements of the ON conditional. In your case, that will be the forums table's id will equal the topics table's column containing the forums table's id (forums.id = topics.forum_id). Since COUNT() is an aggregate function, you then need to run your remaining columns through the GROUP BY.

July 24th, 2005, 11:51 AM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

That's awesome; thanks a lot.

You're quick! Before I could even edit this to retract my problem, haha.

July 24th, 2005, 12:07 PM

Madpawn

My beat is correct.

Join Date: Dec 2004

Posts: 339

Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 4

You need to set an alias and call for that:

Code:

SELECT
 COUNT(1) AS mycount
FROM
 table

Then refer to it as 'mycount' in your scripting language.

July 24th, 2005, 12:09 PM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

Sorry to keep asking, but is it possible to do two of those within each other? For instance to count the rows in 'posts' with a certain 'topic' that has a certain 'forum' specified?

I'm thinking:

Code:

SELECT f.id, f.name, f.description, f.vieworder, COUNT( t.id ) AS topic_num, COUNT( p.id ) AS post_num
FROM forums f
LEFT JOIN topics t, posts p ON f.id = t.forum_id, t.id = p.topic_id
GROUP BY vieworder;

Code:

SELECT f.id, f.name, f.description, f.vieworder, COUNT( t.id ) AS topic_num, COUNT( p.id ) AS post_num
FROM forums f
LEFT JOIN topics t ON f.id = t.forum_id
LEFT JOIN posts p ON t.id = p.topic_id
GROUP BY vieworder;

July 24th, 2005, 12:21 PM

Madpawn

My beat is correct.

Join Date: Dec 2004

Posts: 339

Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 4

Wrong syntax -- try

Code:

SELECT
 f.id
,f.name
,f.description
,f.vieworder
,COUNT( t.id ) AS topic_num
,COUNT( p.id ) AS post_num
FROM
 forums f
LEFT JOIN
 topics t
ON
 f.id = t.forum_id
LEFT JOIN
 posts p
ON
 t.id = p.topic_id
GROUP BY
 f.id
,f.name
,f.vieworder
,f.description

Also note the extra columns in GROUP BY. While MySQL doesn't require it, you should use all fields not being sent through an aggregate function in your GROUP BY, or you can get unexpected results (in other SQL's, not including all non-aggregated columns will give you an error).

July 24th, 2005, 12:29 PM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

It kinda works, but it enters the correct value for post_num into both topic_num and post_num.

#10

July 24th, 2005, 02:10 PM

Madpawn

My beat is correct.

Join Date: Dec 2004

Posts: 339

Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 4

Ah, that's right -- the grouping on the stacked joins is going to mess you up...

I don't think you'll be able to do this without subqueries -- if you're on MySQL version 4.1+, you can try this:

Code:

SELECT
 f.id
,f.name
,f.description
,f.vieworder
,(SELECT
   COUNT(1)
  FROM
   topics
  WHERE
   id = f.id) AS topic_num
,(SELECT
   COUNT(1)
  FROM
   posts
  WHERE
   topic_id
  IN
   (SELECT
     id
    FROM
     topics
    WHERE
     topics.id = f.id)) AS posts_num
FROM
 forums f

#11

July 25th, 2005, 07:46 AM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

No, I'm not, actually. That's okay; now that I know how to do it, I can just make another query. Since I can't perform subqueries, I think I'll need to add a forum_id column to the posts table. Is there some way I can use keys to have it automatically set its forum_id column? If it looks at its topic_id column and gets the forum_id value from the topic with that id.. is that possible without subqueries. Am I talking about foreign keys?

--
EDIT:
I'm upgrading to MySQL 4.1 and hoping that server administrators around the world have done the same. I basically need subqueries..

--
EDIT:
Wow, that was fun, haha. Don't think I want to instal 5.0 after that. Anyways, I had to edit your code a bit, but it's working beautifully now. Thanks a lot for all that help! I know subqueries are going to be very useful.

#12

July 25th, 2005, 12:44 PM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

Too bad MySQL 4.1 "doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'". Or so my error message tells me. But if I remove the LIMIT 1, it complains about having more than 1 row. Looks like I need to make more queries or something unless there's some way around this.

Code excerpt:

Code:

SELECT
(SELECT
 title
FROM topics
WHERE id
IN (SELECT
 topic_id
FROM posts
WHERE topic_id
IN (SELECT
 id
FROM topics
WHERE topics.forum_id = forums.id
)
ORDER BY editdate DESC
LIMIT 1
)
) AS lastpost_topic_title
FROM forums;

#13

July 25th, 2005, 01:42 PM

Madpawn

My beat is correct.

Join Date: Dec 2004

Posts: 339

Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 4

Is this going to be part of the full query we've been working on? And you just want the newest topic title for each forum, right?

#14

July 25th, 2005, 11:25 PM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

Yeah, but I've got it working now. It took a couple redundant columns (like 'title', that also exists in the 'topics' table) in the posts table, but until MySQL allows LIMITs within subqueries, I don't see any other options. Foreign keys or something? Who knows, but I guess as I pretty much have it working, I can leave it the way it is assuming there isnt a better solution.

#15

July 27th, 2005, 09:32 AM

Tylerious

Registered User

Join Date: Jul 2005

Posts: 15

Time spent in forums: 4 h 37 m 15 sec
Reputation Power: 0

I found an alternative. Previously I was limiting results by LIMIT, but now instead of ORDERs and LIMITs, I just use MIN() and MAX(). Now I can get rid of those redundant columns.

Do you think this can be simplified?

Code:

SELECT forums.id, forums.name, forums.vieworder, (
SELECT title
FROM topics
WHERE id
IN (
   SELECT topic_id
   FROM posts
   WHERE id
   IN (
      SELECT max(id)
      FROM posts
      WHERE topic_id
      IN (
         SELECT id
         FROM topics
         WHERE topics.forum_id = forums.id
         )
      )
   )
) AS lastpost_topic_title
FROM forums
ORDER BY forums.vieworder, forums.name, forums.id;

Do the subqueries seem unneedingly complicated to you? I noticed I was referring to the same table more than once, albeit for different data.

#16

July 27th, 2005, 09:50 AM

Madpawn

My beat is correct.

Join Date: Dec 2004

Posts: 339

Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 4

Untested, but see if this works:

Code:

SELECT (
 SELECT 
  title
 FROM 
  topics
 WHERE 
  id =
   (SELECT
     MAX(id)
    FROM
     posts
    WHERE
     topic_id IN
        (SELECT
          id
         FROM
          topics
         WHERE
          topics.forum_id = forums.id)))

#17

July 27th, 2005, 09:57 AM

MadCowDzz

I'm Internet Famous

Dev Articles Frequenter (2500 - 2999 posts)

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

Madpawn, I'm curious myself, what's the purpose of the outer-most SELECT on your query above?
What would happen if one were to remove that?

#18

July 27th, 2005, 10:08 AM

Tylerious