How many mySQL queries is too much?

Started by 127.0.0.1, May 26, 2008, 10:48:00 AM

Previous topic - Next topic

127.0.0.1

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?

Night09

QuotePage created in 0.515 seconds with 27 queries.

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

青山 素子

Quote from: 127.0.0.1 on May 26, 2008, 10:48:00 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.


127.0.0.1

#3
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)
   $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__);

spearfish

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.

青山 素子

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.


SleePy

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 ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

Advertisement: