Tutorial :What is the best way to reduce sql queries in my situation



Question:

Here is the situation,each page will show 30 topics,so I had execute 1 sql statements at least,besides,I also want to show how many relpies with each topic and who the author is,thus I have to use 30 statements to count the number of replpies and use other 30 statements to find the author.Finally,I got 61 statements,I really worry about the efficiency.

My tables looks like this:

  Topic           Reply            User  -------       ----------       ------------  id            id               id  title         topic_id         username  ...           ...                author_id  


Solution:1

You should look into joining tables during a query.

As an example, I could do the following:

SELECT reply.id, reply.authorid, reply.text, reply.topicid,          topic.title,          user.username   FROM reply    LEFT JOIN topic ON (topic.id = reply.topicid)    LEFT JOIN user  ON (user.id = reply.authorid)  WHERE (reply.isactive = 1)  ORDER BY reply.postdate DESC  LIMIT 10  


Solution:2

If I read your requirements correctly, you want the result of the following query:

SELECT Topic.title, User.username, COUNT(Reply.topic_id) Replies  FROM Topic, User, Reply  WHERE Topic.id = Reply.topic_id  AND Topic.author_id = User.id  GROUP BY Topic.title, User.username  


Solution:3

When I was first starting out with database driven web applications I had similar problems. I then spent several years working in a database rich environment where I actually learned SQL. If you intend to continue developing web applications (which I find are very fun to create) it would be worth your time to pick up a book or checking out some how-to's on basic and advance SQL.


Solution:4

One thing to add, on top of JOINS

It may be that your groups of data do not match or relate, so JOINs won't work. Another way: you may have 2 main chunks of data that is awkward to join.

Stored procedures can return multiple result sets.

For example, for a summary page you could return one aggregate result set and another "last 20" result set in one SQL call. To JOIN the 2 is awkward because it doesn't "fit" together.


Solution:5

You certainly can use some "left joins" on this one, however since the output only changes if someone updates/adds to your tables you could try to cache it in a xml/text file. Another way could be to build in some redundancy by adding another row to the topic table that keeps the reply count, username etc... and update them only if changes occur...


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »