Simple Optimization for PHP and MySQL

Saturday April 29 2006 03:23

Here is a list of a few very simple tips for optimizing your php/mysql applications. Keep these in mind while developing.


MySQL


PHP:

If possible it's of course always better to generate static html pages every time something is updated or as often as an update might be relevant instead of querying the database every time.

Further reading:

Make a comment if you have any tips that I've missed and I will add it.

72 Comments

Ian @ 2006-05-04 22:18:56

Uhh, i'm not sure what PHP you're using.. but you can't replace "." with "," to concatenate..

mrkredo @ 2006-05-04 22:19:58

http://forumnyc.com

Calum @ 2006-05-04 22:21:17

Some interesting tips there.
However, I had a very large query with lots of JOINs and subselects that was taking 60+ seconds. Changed to Postgres without modifying the query, and it took <1. Not saying Postgres is faster for small queries, but it handled that massive query a lot better.

Vlad @ 2006-05-04 22:26:00

Page caching can also improve performance greatly.

martin @ 2006-05-04 22:28:05

PHP Version 5.1.1

<?
echo phpinfo();
$variable = "concatenate";
echo "I ",substr($variable,0,255)," with comma";
?>

Works fine for me.

Eric @ 2006-05-04 22:47:37

Corrected:
SELECT id FROM tabell WHERE id = {$_SESSION[id]} LIMIT 1

Eric @ 2006-05-04 22:48:58

Oops:
"SELECT id FROM tabell WHERE id = {$_SESSION['id']} LIMIT 1"

FlorentG @ 2006-05-04 22:49:20

@martin

The comma separator works only with echo : echo accepts a comma separated list, and will output everything without concatenation

marcel @ 2006-05-04 22:56:43

using print is faster than echo

vbebhjhjhjoubwoooyu @ 2006-05-04 22:57:02

9help meiubkja

marcel @ 2006-05-04 22:58:01

ok - ignore my print statement... :(

Anonymous @ 2006-05-04 22:58:02

This will work

echo "I ",substr($variable,0,255)," with comma";

but this will not

$foo = "I ",substr($variable,0,255)," with comma";
echo $foo;

Mr Script @ 2006-05-04 22:59:37

>Don't ask the database for the same stuff over and over again, save >he result.

I do this all the time. i generate new static pages everyday at Mid night

Percy @ 2006-05-04 22:59:54

@martin:

echo "I ",substr($variable,0,255)," with comma";

is the exact same as

echo("I ",substr($variable,0,255)," with comma");

And echo simply "prints" every parameter given to it. Considering the example above uses commas to concatenate a query, I wonder what he's doing with that query? Is he echoing out the query so he can go into mysql and run it manually? From the example above, it sounds like he's doing something like this: mysql_query(('SELECT id FROM tabell WHERE id = ',$_SESSION['id'],' LIMIT 1'));

Bill @ 2006-05-04 23:06:47

Calum,

Are you sure the speed difference was purely due to MySQL vs. PostgreSQL? For example, if your MySQL database was large and built up over time with lots of inserts/deletes, it could have become slow because the data was scattered all over the disk, which can be fixed by doing "myisamchk -R". The mere act of rebuilding the database (whether in MySQL or PostgreSQL) may have cleaned it up a lot.

Another tip: Be consistent about the size of integer columns that you plan to join on. For example, using "WHERE x=y" is very slow if x is SMALLINT and y is MEDIUMINT.

David @ 2006-05-04 23:06:56

While we are doing the little stuff,

php -w old.php > new.php

Will speed up your php code a lot (if you don't use a cache).

Will Bond @ 2006-05-04 23:10:51

A , is not a concatenation operator. You can not do:

$temp = 'string' , 'string';

you can use it to echo multiple items:

echo 'string' , 'string';

----

Re: Don't concatenate when you don't need to.

It is about twice as fast to concatenate the two strings with the variable than to embed the variable in the string.

Anonymous @ 2006-05-04 23:10:57

martin @ 2006-05-04 23:11:33

@ Percy & FlorentG

Yeah you are right, comma is not a concatenator, it just looks like it. The sql example is missleading.

phill @ 2006-05-04 23:21:47

I noticed one problem, he says...

"Use datatypes that fits your data, not too large. For example, INT can hold values up to 4294967295 unsigned, which is often unnecessarily big. Use MEDIUMINT or SMALLINT where applicable. "

But INT's are signed in mysql... So they can only hold up to 2147483647

dade @ 2006-05-04 23:27:16

Anyone know how fast sprintf is compared to string concatenation and/or variable embedding?

Anonymous @ 2006-05-04 23:32:53

"alot" is actually two words, a lot

Jason @ 2006-05-04 23:33:52

while() is faster than for()

martin @ 2006-05-04 23:35:28

@phill

You can have unsigned INTs in mysql

SkinRock @ 2006-05-04 23:43:21

Using the comma to replace the only concatenation operator ('.') in PHP only works for echoing out the output. And because of this, it's no longer even "concatenation", because the echo construct accepts multiple parameters via a comma seperated list (same as any function). Simply put, you aren't concatenating the string, you are only giving a function multiple parameters.

If you still don't believe me, read it from php.net:

http://www.php.net/manual/en/language.operators.string.php

Bill @ 2006-05-04 23:46:40

Only about 1/2 of this is even accurate. The best way to optimize your code is by using xdebug and profiling it. None of these little tricks (the <? ?> less or using '' instead of "") will make any chartable difference. What will make a difference is well written code, which is why using a profiler is so important as it helps you find areas in your code that could be optimized.

martin @ 2006-05-04 23:59:37

I've now edited the mess with comma as a concatenator.

martin @ 2006-05-05 00:09:17

"Use NOT NULL as default" instead of NULL, thanks Deputaats@digg

Anonymous @ 2006-05-05 00:20:37

"EXPLAIN SELECT" should perhaps be done for every query in your PHP application to ensure that your queries are correct and your tables are correctly indexed. Some of the other SQL-related tips are correct, but BEWARE: ALL OF THE PHP TIPS LISTED HERE ARE RUBBISH AND SHOULD BE AVOIDED. http://billharlan.com/pub/papers/A_Tirade_Against_the_Cult_of_Performance.html

Anonymous @ 2006-05-05 00:23:52

Bill is spot-on. Don't uglify your code when you don't need to. Write clean, modular code and use a profiler to optimize the things which matter.

Anonymous @ 2006-05-05 00:30:49

The statement "echo is faster than print" is misleading and irrelevant. There is no practical difference between the two; they should be used as appropriate for the situation.

http://www.faqts.com/knowledge_base/view.phtml/aid/1/fid/40

Premature optimization of kind recommended in the PHP section of this tutorial (the MySQL advice is good, overall) is not useful and should not be used. PHP is not assembly language. Concentrate on writing correct, easily understood code -- then use a profiler to determine where you need to speed things up. Learn to use xdebug.

wouldnt you know @ 2006-05-05 00:55:40

wouldnt you know everyone is an expert and whoever wrote this article is a mindless wanna be who knows nothing...

that very statement above is ALWAYS the conclusion of 99% of the digg community members. Unfortunately, the digg "community" will never prosper to what it could be because of these individuals. Its too bad-

Gabe @ 2006-05-05 01:04:03

Echoing the sentiments of Bill. Optimizing every detail you can is a collossal waste of time. Without profiling the code you are shooting in the dark. I've optimized millions of lines of PHP and 99% of the time the problem is one of the following:

Using linear complexity algorithms where logarithmic are available.
Using exponential complexity algorithms where linear are available.
Poorly written queries, or lack of proper indexing in DB.
Cache thrashing.
File system contention.

You're better off reading http://www.jroller.com/page/rolsen?entry=five_truths_about_code_optimization

what do you know @ 2006-05-05 01:06:28

hey look, someone promoting another site in an attempt to steal diggers!

lol @ linear and log algorithms. This is php programming, not an excel graph

Aaron Schmidt @ 2006-05-05 01:16:57

"# Don't put things that changes very rarely in the database, instead put it in a global array in some include file."

Instead of splitting up data between some include file and your database, the better option would be to use some application caching module (such as APC or memCache).

http://pecl.php.net/package/APC
http://pecl.php.net/package/memcache

Nathan Schmidt @ 2006-05-05 01:20:16

"lol @ linear and log algorithms. This is php programming, not an excel graph"

There's a reason universities call it 'Computer Science' rather than 'Writing code and stuff'

Jack9 @ 2006-05-05 01:34:06

What do you know, what do you know has no knowledge of why using appropriate algorithms matters in any modern language and syntactic sugar does not. Perhaps this is why many don't consider PHP 'modern'? The PHP community is filled with morons.

what do you know @ 2006-05-05 01:50:35

"There's a reason universities call it 'Computer Science' rather than 'Writing code and stuff'"

^^ HAHA!! this coming from a guy who's website wont even load due to a parse error. Get to fixin buddy. Maybe study that ol' computer science , twat.

David @ 2006-05-05 01:51:29

Thanks Bill for the heads up on XDebug - just installed it and WinCacheGrind - works great!

what do you know @ 2006-05-05 01:52:56

Jack9,
You are a moron, PHP is losely typed.

rottle @ 2006-05-05 01:59:09

A lot of these will get 0.001 percent boost in execution time (if everything is to be followed listed here) and will really do not much to speed up the app in general. If you declare just one variable in php, it's all those tiny speedups going to waste.

Wrzl @ 2006-05-05 02:01:21

Thanks for this great article.
I recommend PEAR cache_lite for great preformance on heavy trafficed sites. And also eAccelerator is a great way for php caching.

red owl @ 2006-05-05 03:53:36

pack code "delete any indent ", and close ?> for output html
<?
if($var==5){

code(
code(
}

if($var==5){ code( ...code( } (dev php example +30% fast

close and open tag
<?
php code.....
?>
<div><table>.......<?=$var;?></html tag>
-----

if($variable=="fixed value")

slow if(("fixed value") ==$variable)
check in for and microtime (repeat 10000)

use switch to exclude unused code
use number and non string 4 is number "4" is string

switch($_GET['condiz']){
case 1:
include("miocodice.php"); // 200 line
break;
case 2:
include("miocodice2.php"); // 500 line
break;
}
-----

other incorretc metod
<?
function myfunction($var){
code here
}

function myfunction2($var){
code here
}

function myfunction3($var){
code here
}

$function = $_GET['val']; // 1
switch($function){
case 1:
function1($value);
break;
case 2:
function2($value);
break;
.....

}

correct fast execution

<?


$function = $_GET['val']; // 1
switch($function){
case 1:
include("file_function1.php"); //structured if not recursive or reused
break;
case 2:
include("file_function2.php"); // this file ignored (delete this for chek)
break;
case 3:
include("file_function3.php"); // this file ignored (delete this for chek)
break;
}

if the funcion contains 500 line code php read complile 520 line
not 1520

! allocate resource if not use declared function; include the file contain only used function (code exclusion)


if use define in language application include file
contenents only used defined string


use the function if re use code >1 else use structured metod
use internal function, optimize code (small execution code is GOOD)
use class and function in you program if use more 1 <1

use example
if(empty($_SESSION['language'])){
read language from $_SERVER.....
}

Mike @ 2006-05-05 04:03:50

There is no big difference between print and echo... some tips are just plain dull

Me @ 2006-05-05 04:17:25

Dude - have you ever run an execution trace for some SELECT commands? Most common ones run faster if you you "SELECT *" because the results are pre-cached. When you specify a few fields - it will run a SELECT on the whole table anyway and then take extra time to limit the output to what fields you asked for.

catfarm @ 2006-05-05 04:19:34

to all you people saying how this will only speed up by .001 percent and whatnot, you are sorta (i think that number is a bit low) correct. however, if you do these things as standard practice when you are building stuff to begin with you can get the best of both worlds... perhaps you should read the second sentence in the article... i wouldnt go fishing through thousands of lines of code to do these things, however i pretty much do all these things by default... so should you.

Sam Liddicott @ 2006-05-05 08:49:59

ESCAPE your data to protect from sql_injection:

This is BAD BAD BAD
'SELECT id FROM tabell WHERE id = '.$_SESSION['id'].' LIMIT 1'

This is better:
'SELECT id FROM tabell WHERE id = '.mysql_escape($_SESSION['id']).' LIMIT 1'

Sam

Niek @ 2006-05-05 09:36:59

@Sam:

The setting magic_quotes_gpc is enabled by default since ages, so escaping SQL string isn't needed (unless you run the code on a box with magic_quotes_gpc disables, but in that case you're simply stupid).

Keith @ 2006-05-05 11:16:00

How much efficiency are you going to get from this revised way of coding PHP?

I feel it's just a different way of writing, nothing to do with bad (or buggy) coding.

Therefore, in so doing, how much is the speedup or optimised is the result going to be?

Netizen @ 2006-05-05 11:26:00

@Me: The MySQL manual explicitly mentiones to _not_ use SELECT * except for quick testing or debugging purposes.

@Niek: Magic quotes are outdated and will be removed in PHP6. The better and more reliable way is to use prepared statements (with PDO for example). Even if these are not available you shouldn't rely on MQ, but use the proper DB-specific escaping functions instead (mysql_real_escape_string() for example).

n!

Niek @ 2006-05-05 11:43:22

@Netizen:

Using mysql_real_escape_string() with magic_quotes_gpc enabled has zero advantage, and you'll have to execute stripslashes() on the data first. Talk about optimization...

Netizen @ 2006-05-05 11:54:05

@Niek: I don't talk about optimization, but reliable and secure code. Magic quotes don't necessarily capture all characters that have to be escaped for a particular database.

And yes, I _do_ strip slashes if magic quotes are enabled and I can't disable them on the server, simply because I always want to work with the raw data. Then, when some encoding or escaping is necessary, I use the proper functions for that so I don't have to rely on some ugly automatism.

n!

Anonymous @ 2006-05-05 15:28:09

Don't concatenate when you don't need to.
"SELECT id FROM tabell WHERE id = {$_SESSION[id]} LIMIT 1"

is faster than:

"SELECT id FROM tabell WHERE id = ".$_SESSION['id']." LIMIT 1"

---

Both of which are slower than:

'SELECT id FROM tabell WHERE id = '.$_SESSION['id'].' LIMIT 1'

Using single-quotes in place of double-quotes is perhaps the most common (and under-rated) speed optimisation.

Moosh @ 2006-05-05 16:24:45

Translated in french on my blog
Traduit en Français sur mon blog

mohrt @ 2006-05-05 16:40:22

alternate to doing this:

$maxvalue = 100/10;

for($i=0; $i<$maxvalue; $i++){

// Some code

}

is this:

for($i=0,$j=100/10; $i<$j; $i++){

// Some code

}

Percy @ 2006-05-06 07:37:15

Magic Quotes are a joke. I'm so glad they're going to remove it permanently in PHP6. Magic Quotes was implemented to make up for programmers who didn't think to do any kind of input verification and/or escaping values. I cannot express how much I hate magic quotes... The last 3-4 years I have forced my websites to run on magic_quotes = off servers.

Magic Quotes goes against a basic concept for programming. Why add_slashes() to something just so you have to strip_slashes() later? Why not just keep it normal, then add_slashes() or escape it in whatever format required when it is needed.

mike sMith @ 2006-05-07 18:15:28

PvUtrix @ 2006-05-08 02:26:15

This tips might make a tiny difference now, but will be completely insignificant in 5 years when we have much more powerful computers...

James Laver @ 2006-05-09 11:08:58

You should learn by now that quality of code is far more important than execution speed, especially in an interpreted language like php.

I'm not arguing with most of this, but the thing that sticks out is the suggestion to use large code blocks as opposed to little ones. Where it is clearer, you should use larger blocks, and where it is not, you should not.

Also, it's important for this reason to start using mysql_fetch_object, apart from making more sense, it's much easier to type

Anonymous @ 2006-05-10 18:49:31

Hmm

geoffrey @ 2006-05-15 15:45:59

I'm surely not going to follow advices from someone using short php tags...

Brantone @ 2006-05-17 21:57:37

" Note: This only works with echo, which is a function that can take several strings as arguments. "

umm ... not to be nit-picky, but:

http://ca3.php.net/manual/en/function.echo.php

clearly states:

"echo() is not actually a function (it is a language construct) ...."

Anonymous @ 2006-05-17 22:00:21

Anonymous @ 2006-05-20 01:03:05

Anonymous @ 2006-05-20 01:03:12

Anonymous @ 2006-05-20 15:05:46

Rebecca @ 2006-05-20 23:34:48

Nice site!
[url=http://rfqoeyow.com/shyb/uytg.html]My homepage[/url] | [url=http://spcofntv.com/fndd/tshp.html]Cool site[/url]

Brad @ 2006-05-20 23:34:51

Nice site!
<a href="http://rfqoeyow.com/shyb/uytg.html">My homepage</a> | <a href="http://cpsxqjjg.com/onqh/njcr.html">Please visit</a>

Gabriel @ 2006-05-20 23:37:09

Good design!
http://rfqoeyow.com/shyb/uytg.html | http://dastunwh.com/uxku/deir.html

Anonymous @ 2006-05-27 05:39:38

Anonymous @ 2006-05-27 05:39:50

Anonymous @ 2006-05-27 05:39:56

Make a Comment

This article is hosted by dublish.com which is a free online publishing tool for those who have something to say but don't have their own blog. If you find this article abusive, report it to us.