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.

Today, about 2 hours ago. Domain started to point my new host. I got some problems with wp, because paths on new host was different, so there was a 2 hour down-time. (but I think it is not a big thing on this blog)

As I first copied all of my wp stuff to new host and recreated db, it didn’t work. Solution was very simple.

1. Install wp to new host
2. Drop every table on new database
3. Upload database dump with data you want
4. Upload theme- and plugin-files
5. It works!

And as I haven’t blogged for 2 weeks I probably blog much more as normally for some time. Actually, it was pretty annoying when I wasn’t able to blog anything.