PHP optimization, speed, cpu usage *HELP ME*

[jeromepelletier]

Alright ill explain and go through some things first, what i need to do is lower my CPU usage on my site, im not exactly sure what to do, ive read through things on the web but its still unclear, also id like to improve load time saying, might as well go for a 2-in-one!

First of all, when selecting something from my database, i use:

$firstvar="SELECT * from database where something='$something'";
$firstvar2=mysql_query($firstvar) or die(mysql_error());
$firstvar3=mysql_fetch_array($firstvar2);


My first question is, do you recommend a better way to do this? I noticed in some other peopels sites, they use a shorter or almost completely different way. Secondly about the above, the "Select * from" do you recommend selecting only what i need? Sometimes im oinly getting 10 things, somethings more or less. Thirdly, i dont have anything indexed, should/how do i do that, ive read about it but it just doesnt make sense...I use the above many many times in my scripts, if there is a way to improve them, i think my codes would be a lil bit better.


When updating the database I use


$varupdate="update database set thing=123 where ID='$thingsID'";
mysql_query($varupdate) or die(mysql_error());


About the above code, again, is there a better and faster way of doing so? (by fast a mean load the page faster and by better i mean make the CPU usage less heavy)



Now that i got the database thing down, onto the actual code!

Things I dont do in my code:
1) Using a footer & header
2) Caching
3) 1 and 2 are all i can think of....

For headers and footers, are they better? As in run faster and not so bad on CPU usage? I should explain my code a bit I guess ... What I do i have 1 main file, then when another is needed, such as someone clicks maybe Buy This, then the file buythis.php is called up using include 'fileexample.php' in the main file. IF a header and footer is better then what im using, and is highly recommended, im guessing thats another thing to do.

For caching, i just recently saw this online in a site, something about opcode and such that can speed the site up by 200%, is this true?

Some other things are, i do have a image background, i do have image borders for my tables, theres a semi amount of text (mainly the chat is the killer for text)

SO, what do you guys think i should do?

[firepages]

Don't worry about headers and footers etc , they wont affect your CPU load, nor will the amount of text/images etc, they may affect parse time and response time but thats about it.

MySQL is another matter ...at the very least you should index your MySQL tables, as a very basic rule if you need to sort or query on a field then that field should be indexed (ideally the field will be an integer type though thats not always realistic)

So with ..
"SELECT * from database where something='$something'"
if you use that query a lot then `something` should be indexed & yes it is more efficient to `SELECT field1,field2,etc` than `SELECT *`

That said the indexing itself can work miracles... not that its a cure for bad database design but one step at a time :D

caches etc all can make a difference but the basics are more important and in your case its probably the DB design/indexing etc.

[jeromepelletier]

alright thanks :) and thats a joy hearing footers/headers wouldnt help :P didnt really want to make that switch...

As for the mysql, ima spend a crap load of time editing and such to select only whats needed :) And as the indexes, you said there usually numbers? Can they be letters too, such as names? ANd ive been looking around and now im just confused about indexes, what are they? I thought they were the key of the table...but now im thinking otherwise *is ashamed of saying it*

[GJay]

If you think of a database as being a filing cabinet, then with no indexing whatsoever, you'd have to start at the beginning, and work your way through the records until you found the one you were after, this isn't good.
With a primary key, imagine each record has a little tab sticking up from it that identifies it, and they are arranged in numerical order, thus searching on a primary key is faster.
You could, if you wanted, get a second filing cabinet, where each record just stores where to find the actual record in the first filing cabinet, but the tabs on these records are for a different field- surname maybe- and they are ordered appropriately. So searching for a surname is now quicker, but there's effort involved in creating this index, and storage needed, and when you create a new record, you've got to remember to put the value in the index-cabinet, so you've got to decide which indexes are worth going to this effort for.

Indexes in a nutshell (the analogy is a little off in places, but gives the general idea).

[jeromepelletier]

alright, so a key of the table is the index then...:)

And to add another key, say surname, are you saying make a new table listing the surnames with the key IDs form the other table in order? select that in the begginning of the code, store the ID it got, then just use that when going through mysql?

[GJay]

no, taking the analogy too far.
In MySQL you want to use the CREATE INDEX command, then you search the original table as normal, and let mysql handle how the data gets found.
Think of the index as being a little card-box sat on top of the filing cabinet, that your secretary looks through when you ask for Joe Bloggs' details :)

[jeromepelletier]

no, taking the analogy too far.
In MySQL you want to use the CREATE INDEX command, then you search the original table as normal, and let mysql handle how the data gets found.
Think of the index as being a little card-box sat on top of the filing cabinet, that your secretary looks through when you ask for Joe Bloggs' details :)
ahhh ok :) thanks! Ill edit all my select * fields and my fields not using indexes :)

[cdwhalley.com]

mySQL queries generally take up most of the time a script is running, but also look if you have any big loops in your code, especially if they have function calls inside them, as they can be quite slow.
Also, one optimisation that you might find useful, although I have no idea if you use this in your code, is if you want to count the number of rows it is quicker to use mySQL's COUNT as supposed to getting everything from the database and then counting it with PHP.
E.g.:

$result = mysql_query("SELECT COUNT(*) AS fem_count FROM users WHERE gender='F'");
$row = mysql_fetch_array($result);
extract($row);
echo $fem_count;

Is quicker than:

$result = mysql_query("SELECT * FROM users WHERE gender='F'");
$fem_count = mysql_num_rows($result);
echo $fem_count;


or so I've found, anyway...

[Beagle]

http://www.zend.com/zend/art/mistake.php?article=mistake&id=128&open=1&anc=0&view=1

Here's a link to a PHP article on best/worst practices. Could help you out a little bit.

The best way to fix things, though, isn't to learn practices, you should be learning some theory. Algorithmic efficiency is king. Once you understand how to calculate the cost of simple high level algorithms, then listening to the technical mumbo jumbo about how system calls and memory usage work starts to make a little more sense.

As for SQL efficiency, you'll need to read up on "database normalization" and various database design techniques to speed up your DB usage. When you begin to understand that, you'll be able to say with confidence which sections of your database are read heavy, which are write heavy, which need to be able to do both quickly, and you can design them accordingly.

What I'm trying to say is that if you're truly interested in speeding things up in a significant way, you're not going to get your answers from a thread on a forum. Get some books and learn how to write your own test cases (don't take everything said on faith, test it!).

Good luck!

[jeromepelletier]

WOW how did i ever live without indexing stuff.... I indexed most of my things i used a lot...my page usually took on average 0.2 seconds...now it takes on average 0.06 seconds... dont know if it effected my cpu much....wont see until i get a high load :) Anyways im still working awya at it :P


eh nvm,...im still only doing like 0.2 seconds :S