What could be making my website take up so many resources?

[purbeast0]

Luckily I've known my web host for a long time, or else my site would be taken offline he said.

I don't really know where to start. Basically, what could be making a website take up so many resources? My main page is just listing a bunch of stuff that is pretty much pulled from an SQL database.

However the DB it's pulling information from has already 80K records in it, and it is growing by about 1K a day. Could that have something to do with it? Could it be the way that I'm pulling the information that is taking up so many resources?

I haven't taken courses in SQL or anything so I'm not really sure what I could change to optimize it. When I do SQL queries, I am doing 'SELECT *' instead of selecting individual fields. Should I instead ONLY be selecting the fields of the table that I use? Could that be making THAT much of a difference?

My assumption is that the resources are being used from the DB just for the sheer fact that my page basically lists information in a table pulled from a DB, but as I said, it's a large one. Also I am automatically having fields inserted to the DB, at around 1K/day. Could that be causing some type of resource issue as well? My total index.php file is almost 100Kb as well. Could that be causing some issues?

Any suggestions or areas I shold look at would be appreciated. And it's not the site that's in my signature either.

 

[troytime]

select only the fields that you use

whats your WHERE statement look like?
does your table have any indexed columns?

 

[purbeast0]

Originally posted by: troytime
select only the fields that you use

whats your WHERE statement look like?
does your table have any indexed columns?

Well I have quite a few select statements.

None of my tables were indexed at all, but I just did that actually and I saw a speedup on some queries of 66% (from .0033s to .0011s) so I figure that did a decent amount of help 🙂

I'm also going to change my select statements to make sure I only get the columns I need.

Thanks 🙂

 

[jfall]

sloppy coding

 

[Mark R]

What exactly are your queries like?

How many fields are you pulling with each query? And are you doing all the filtering with SQL? (i.e. do you run a SELECT statement, then throw some of the data away, because you don't need it for display)

Do any of your queries use joins? If so, are all the joined columns indexed. Again, if you're selecting more fields than you actually need, you may be forcing SQL to do extra work (selecting unnecessary fields from a join can cause significant performance hits on large queries).

Are you using any 'vague' criteria in your WHERE clause - things like SELECT * WHERE Name LIKE '*smith' is not indexable, and SQL has to scan every single entry in the table (which is slow and resource intensive).

Another thing that slows thing down for complex queries is the use of SQL commands. Things like

mySQLstring = "SELECT ID, Description, Price FROM Items WHERE Price = " + PriceString
runSQLCommand (mySQLstring)

should be avoided. As they must be recompiled each time, and are a potential security weak-point (you must escape any user-entered data, and make sure that it is escaped properly).

Actually parsing the SQL command and working out the best way to run the query can take as much as 5-10x as long as running the query. If you use stored procedures, they do not need to be replanned each time - only when lots of changes have been made to the database. [it should be stressed that tihs is only an issue for compelx queries - e.g. where you are joining 4 or 5 tables or you are calculating lots of totals, etc.]