Advertisement:

Author Topic: How many mySQL queries is too much?  (Read 4617 times)

Offline 127.0.0.1

  • Jr. Member
  • **
  • Posts: 265
  • Gender: Male
How many mySQL queries is too much?
« on: May 26, 2008, 10:48:00 AM »
Hi, I am a user of SMF 1.1.x

My board makes a total of 7-9 queries on the /forum/index.php page. However, when I include SSI includes (SSI.php) on the page, the queries go up to 13-15.

Is that too many queries? Is there a way to cache queries or does SMF do this automatically?

Offline Night09

  • SMF Hero
  • ******
  • Posts: 1,940
  • Gender: Male
Re: How many mySQL queries is too much?
« Reply #1 on: May 26, 2008, 10:53:54 AM »
Quote
      Page created in 0.515 seconds with 27 queries.

Thats what my homepage says and it has run fine with no problems.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,072
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: How many mySQL queries is too much?
« Reply #2 on: May 26, 2008, 11:32:20 AM »
Is that too many queries? Is there a way to cache queries or does SMF do this automatically?

It is only too many queries if your host has some kind of limit on how many queries you can do per hour. Most good hosts have either an exceptionally high limit or no limit.

If you use caching (APC, eAccelerator, etc) then many queries will be cached for you. Your host must either support this (if you are on shared hosting) or you must install a compatible caching engine (if you have a VPS or dedicated plan).
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline 127.0.0.1

  • Jr. Member
  • **
  • Posts: 265
  • Gender: Male
Re: How many mySQL queries is too much?
« Reply #3 on: May 26, 2008, 12:16:42 PM »
Okay. Thanks for the feedback.

One more question...

On my page I have a query that displays the top users. The top users rarely changes and I have read it is good to cache things that do not change much.

My host does not use caching such as (APC, eAccelerator, etc) so how would I go about caching a MySQL query such as this...

This is my query that gets the top posters (who are not banned, and who have a profile pic)
Code: [Select]
   $request = db_query("
      SELECT m.ID_MEMBER, m.realName, a.filename
      FROM ({$db_prefix}members as m)
         LEFT JOIN {$db_prefix}attachments AS a ON (a.ID_MEMBER = m.ID_MEMBER)
      WHERE m.is_activated = 1 AND a.ID_MEMBER = m.ID_MEMBER
      ORDER BY $sortOrder DESC
      LIMIT $startPoint, $maxDisplay", __FILE__, __LINE__);
« Last Edit: May 26, 2008, 12:43:15 PM by 127.0.0.1 »

spearfish

  • Guest
Re: How many mySQL queries is too much?
« Reply #4 on: May 27, 2008, 01:23:15 PM »
The query limit really depends on the host.  If it gets too high for a shared account, you may need a VPS or dedicated.

I have no experience in terms of caching, but you may want to consider setting two cookies: one that stores the data and one that stores the time.  Each time it goes to grab that information, it checks whether there is data, and if that data was updated within the last xxx time.  If there is cached data present, it uses that instead.

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,072
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: How many mySQL queries is too much?
« Reply #5 on: May 27, 2008, 02:42:35 PM »
I believe there is a way to run the query through SMF so you can use the caching with SMF (you need to tell SMF it should be cached), but I haven't touched this area and thus don't know how to do so.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,373
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: How many mySQL queries is too much?
« Reply #6 on: May 29, 2008, 01:01:12 PM »
Well if there is no accelerator it doesn't do good to tell it to try and cache something when it would fail.

SMF 2.0 has a file based cache for those who do not have an accelerator installed. It works fine.
I tried the other day to back port the changes to 1.1, but it actually caused load on my forum to go up. So I disabled it for now.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub