Hey guys, I'm just working on a small php project (blog) and was wondering whether a basic select command as the one posted below could be optimized in any way (measured in loading time and CPU usage). Feel free to correct wrong syntax or whatever u find.

Title is title obviously, prev is preview which is a short description. Sorry for using small variable names, but it's a bad habit that doesn't bother me in these small scripts. Anyway, have fun and thank you for helping / debating.

PHP Code:

<div id="b">
// 10-15 lines of html here
<ul>
<?php 
flush();
$a=mysql_connect('localhost','user','pass');
mysql_select_db('database',$a) or die();
$b=mysql_query('SELECT id,title,prev FROM b1 ORDER BY id limit 5');
mysql_close($a);
while($c=mysql_fetch_array($b)){
echo '<li><h2><a href="#">'.$c['title'].'</a></h2>'.$c['prev'].'</li>';
}
?>
</ul>

Not that i can see.

Its pretty specific already.

You can try stored procedures. Compiled statements will help improve the performance, but you need to have more data to see the difference.

If "id" is a primary key here, the query of such kind cannot be any faster than now. Though you may tune mysql cache if this query is requested significantly more often than new row is inserted, to make your mysql return the cached result instead of fetching the data each time. In this case you should keep in mind that mysql cache of all queries for the given table is reset every time a change is made to a table. Any change, not only insert.

As already pointed out, you won't see any differance unless you handle large amount of data. Since you're already using a limit of 5 rows, no matter what changes you were to make the time for just running the query will just be 0.00 seconds.

Thanks for the helpful replies, this is very theoretical question, but which of the following two examples would give the best performance? Lets assume that I changed the limit from 5 to 5000.

PHP Code:

<a href="'.$c['title'].'</a></h2>'.$c['prev'].'</li>'; 


Where the title includes both the url and the title, or:

PHP Code:

<a href="'.$c['url'].'">'.$c['title'].'</a></h2>'.$c['prev'].'</li>'; 


Basically the question is whether to go for less sql lookups at the price of bigger sql cells? Another question is how PHP will react to the inappropriate " (quotation mark)? The script does work, but will php be slowed down by the quotation mark?

Thanks for the help guys

You definitely are optimizing wrong things in wrong place. The difference in these particular examples will be 0.0001%, it isn't even worth discussing.

Originally Posted by mtishetsky

You definitely are optimizing wrong things in wrong place. The difference in these particular examples will be 0.0001%, it isn't even worth discussing.

First of all, my question was theoretical, secondarily your argument is invalid. This isn't about optimizing, it's about learning to code without wasting resources on things that could be solved in a more innovative way. I agree with you that this example is absolutely a waste of time, but you failed to see the big picture.

Could anyone answer my last question please?

No matter how theoretical, or how big you scale this problem, it won't make any difference. Weather you retrieve 2 cells with x data, or 1 cell with 2x data, the same amount of data must still be retrieved. Maybe it will differ on some bits, depending on the internal structure to store the data, but the size of your application to actually notice any differance must be ridiculous.

Just choose the more logical one, which makes your job easier. In this specific case it makes more sense to store title and url individually.