brady.k — Thu, 19 Jul 2007 01:11:07 +0000

2 and 3, I'm not exactly sure. I can look into it and get back to you.

5, my favorite thing to do is this...

Pretend your database connection is something like

<?php
$db =mysql_connect(....balalalhahalahablah);
?>

And then in your footer (or whatever you call your last includes file that runs all the things at the end of the page), you can do something like

<?php
if (($db != NULL) || ($db != '')) {
  mysql_close();
}
?>

That will close all of your MySQL connections, but won't attempt to close any if you never had any in the first place.

A quick and easy fix to a very big problem!

sublimer — Wed, 18 Jul 2007 21:22:50 +0000

Trust me, I would love to show you the snippets of code, but I don't have access to them right now. It's running on my alum high school's intranet. I am out of town right now and accessing it would be a job in itself.

That's why I was asking for general ideas about what slows it down. An IT guy from the school called me and notified me of the problem. I developed the code so I am very familiar with it.

For brady.k

For number 1, I do not think it is the server because it has no problem running other sites on the same server, such as the school's website and other teacher applications.

For 2 and 3, We installed PHP ourselves and did very little configuration because we were running out of time to develop the program. By default, is PHP caching enabled?

For number 4, I am not doing my statements like that.

For number 5, I was unaware that closing connections could change performance. I know that I am not doing that. I am a self-taught PHP/MySQL person, so often times I miss those little tid-bits. I will inform IT about adding the code to close the connection.

For pr0gr4mm3r

There are approximately 2,200 rows. Split up into 10 columns.

I'll try to get you some code to help you guys out. I appreciate the ideas in the meantime though!

brady.k — Wed, 18 Jul 2007 04:55:00 +0000

I was thinking about this today, and I decided you have a few more things you might want to look at...

1) Why are you so sure it's not the server? Do you have enough RAM? Is some of the RAM faulty? Is the processor functioning correctly? Is the processor strong enough to do this along with everything else?
2) PHP has an inherent caching system. Are you using PHP? If so, is it configured for caching?
3) I believe MySQL has a caching system as well.
4) How are you processing your UPDATE calls? Are you using location methods that aren't efficient (for example, UPDATE...WHERE this_field LIKE = ' %$something%' ... instead of UPDATE... WHERE id = '$id')
5) Do you have faulty / improper connections to the db? I forgot to close a connection on a site once, and the database performance seriously suffered. When I found this, and corrected it, performance increased exponentially... always keep track of your connections, and be sure to close them so you save system resources.

But to really help you, we need more information from you. Specific code, site stats, db stats, maybe some server info... But I've told you this before, and you haven't given us anything.

pr0gr4mm3r — Tue, 17 Jul 2007 23:50:49 +0000

Insert delayed statements may improve your performance, but it seems like your problem is worse than an optimizing issue. How many columns do you have indexed?

brady.k — Tue, 17 Jul 2007 19:27:49 +0000

I'm assuming that you are using MySQL since you didn't directly answer the question...

Can we see some of the queries? You may be doing things that will slow it down, despite the server's speed.

Also, you can always look into something like memcached, or a home grown solution, that only processes INSERT and UDPATE queries at a predefined interval as a batch, and uses something like a LUT (look up table) for SELECT queries... all rather than do it directly to the database. The whole idea is to make it faster (Facebook and LiveJournal are big memcached users)

sublimer — Mon, 16 Jul 2007 19:46:51 +0000

brady.k;221701 wrote: Any standard relational database should be very quick.

Murphy's Law.

Well, its only when using INSERT or UPDATE when the query takes a while to execute. SELECT works fine.

There is roughly 2000 records in the main table.

It was running fine but started to slow down once there were a lot of records added to the database. The more records added, the slower the database.

If it's not the server, what could be done to try and speed it up?

brady.k — Mon, 16 Jul 2007 19:06:20 +0000

Is this a MySQL db? Any standard relational database should be very quick... whether it's MySQL, MSSQL, Oracle, etc.

It's either your server (need more RAM or disk space?), or your script (efficiency?).