Is “SELECT column1,column2 FROM table“ faster than “SELECT * FROM table“? Should I only pick data that I need, or just take everything?

I personally like to take everything, because it is much easier to develop applications that way, but many sites recommends to take only data that is needed because of speed.

So, I wanted to test this and see if there is a major speed difference.

On small databases (something like 10-1000 rows) there is almost none difference. I wanted to see the difference on a huge database, so I wrote little script that will fill the database with 1 million rows of random data. Then I coded another script for testing the speed differences on SELECT query.

Scripts used to build the test

Here is the database structure:

mysql> desc test;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| ID      | int(11)      | NO   | PRI | NULL    | auto_increment |
| field_a | varchar(500) | NO   |     | NULL    |                |
| field_b | varchar(500) | NO   |     | NULL    |                |
| field_c | varchar(500) | NO   |     | NULL    |                |
| field_d | varchar(500) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

And here is the script that I used to fill the database:

<?php

echo("Connecting to the database...\n");
mysql_pconnect("localhost","lauri","") or die();
mysql_select_db("test") or die();

echo("Starting to fill the database...\n");

$amount = 1000000;
for($i = 0;$i < $amount;$i++){
$rand1 = random();
$rand2 = random();
$rand3 = random();
$rand4 = random();

query("INSERT INTO test(field_a,field_b,field_c,field_d) VALUES('$rand1','$rand2','$rand3','$rand4');");
echo("Value: $i \n");
}

--snip--

?>

(In the –snip– there is only the random() function creating random 9 char strings, and my query function that does normal querys to the database)

And here is the script that I used to test the SELECT speed:

<?php
echo("Connecting to the database...\n");
mysql_pconnect("localhost","lauri","") or die();
mysql_select_db("test") or die();

$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
echo("Executing full select\n");
$result = query("SELECT * FROM test");
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];

$diff = $etimer-$stimer;
echo("TIME: $diff\n");

$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
echo("Executing partial select\n");
$result = query("SELECT field_a FROM test");
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];

$diff = $etimer-$stimer;
echo("TIME: $diff\n");

--snip--
?>

(Snip contains the database query function, I stole found nice timer script from desilva)

Results

I executed the testing script against 1 million rows of random data, here is the results:

Connecting to the database...
Executing full select
TIME: 1.77005004883
Executing partial select
TIME: 1.03216195107

So there is only 0.7 second difference with million rows of data…

In my opinion, the 0.7 second difference is so small that if you are working on project that doesn’t have massive databases, you shouldn’t worry about selecting only fields that you need, but if you are working on something that has lots of users and huge databases, selecting only data you need may help speed of the script lot.

After the tests I realized that the varchar size of 500 is way bigger what I used… I changed the varchar size to 10 and executed the tests again. Results here with varchar(10):

Connecting to the database...
Executing full select
TIME: 1.71709799767
Executing partial select
TIME: 0.975940942764

Not huge difference either, but if you need all the speed you can get, make the sizes of columns right… Not way too big like I did.

System

I used my home-server with 3Ghz Pentium 4 processor with hyper-threading. 2 gigs of ram and normal 7200 rpm 80g hard-disk. Operating system was Debian with normal installation of PHP5 and MySQL via apt-get with default settings.

I have recently started new php project. It is my own php engine/framework.

I have tried couple frameworks and all of them have been pretty nice. I especially like cakephp.

But the problem with these sometimes is that they force too much where what code should be placed. Example the Model View Controller stuff. The idea is great idea and I have nothing againgst it, on some small project cakephp/any most of the frameworks are way too massive. (in my opinion of course)

But if I code from scratch and place all if my code where it feels good to place it… Usually something is somehow in wrong place. This is when I get the idea for my own framework.

Napalm engine framework (or NeF in short) is a php framework that doesn’t make creating code any faster. It just helps to keep code where it should be. It should help managing code base on small/medium project.

This framework also won’t force user to place some code on some place. It only offers way to manage code in standart way. If user doesn’t want to use all of the elements, rest of the system won’t break.

NeF will also be simple to learn, because there isn’t any kind of complex API.

The idea of application workflow is following:

Actions are things that gets users input, does querys to database, handle data, etc.

Render segment is in 2 parts:

• View is a HTML document with php links to blocks
• Blocks is a piece on a view. Example a content area/navigation.

Then there is 4 different helpers. Helpers are small code snippets that should be executed, but really aren’t part of actions/views

• Pre-action helpers – stuff that is executed before actions
• Pre-render helpers – before render
• Post-render helpers – and after render
• Normal helpers – can be used anywhere in code, called by specific function.

And if I will ever get this done, I will publish this under open source license.