Posted by: phillipnb | July 7, 2011

PHP: Analysing penalty for fetching unnecessary column names in sql


Some time back I read about the best practices in php and one of the items there that created kind of confusion within me was the use of “Select *” in sql query. While some php enthusiast said that a).unnecessary columns fetched will add expense to the data retrieval time, b). the more data is read from the tables, the slower the query will become, there are others who have said the opposite – that is, it does not matter whether you select the column that you need or select everything using ‘select *’. To decide which of these claims is true – I tried to test it out by writing a php code which involves selecting everything and selecting just the columns that you need. Here are the results:

My Environment:

Hardware: Dell Inspiron 1525 Intel Core 2 Duo T5800 CPU 2 GHz 32 Bit OS with 3 MB RAM, Windows Vista Home Basic, PHP 5.3.6, MySql Server 5.5

Experiment One:

In this experiment, we will measure the time it takes to fetch using the following types of sql query:

  • select * from table
  • select all columnnames from table

Number of times(trials) this was tested: 5

The time for both the queries is show below:

Number of fields in the table is 1
[Here ‘select *’ and ‘select columnnames’ will fetch the one field in two different ways]

Number of records time(ms) for select * time(ms) for select column name
1 0.0010821342 0.0008967876
100 0.0020200729 0.0036632061
1000 0.0114593029 0.0113101959
10000 0.0751474380 0.0763902187
50000 0.3934387683 0.3932191371
100000 1.0344342231 1.0399250030

Number of fields in the table is 25
[Here ‘select *’ and ‘select columnnames’ will fetch the 25 fields in two different ways]

Number of records time(ms) for select * time(ms) for select column name
1, 0.0013939380 0.0012606143
100 0.0041572093 0.0059715270
1000 0.0247195720 0.0254330158
10000 0.2333475589 0.2477362155
50000 1.3948455810 1.4656117916
100000 4.3888415813 3.8750253677

Number of fields in the table is 50
[Here ‘select *’ and ‘select columnnames’ will fetch 50 fields in two different ways]

Number of records time(ms) for select * time(ms) for select column name
1 0.0015746116 0.0014937877
100 0.0056658744 0.0055483818
1000 0.0444685459 0.0496345996
10000 0.4901227951 0.5354696273
50000 2.4513376235 2.5967393875
100000 8.6993484020 7.3671073913

select * vs select all

Experiment Two:

In this experiment, we will measure the time it takes to fetch using the following sql query:

  • select * from table
  • select field0 from table

The aim of this experiment was to decide whether we wasted resources and degraded performance by selecting columns (using select *) which were not required. For example: In a code, if a programmer required only one field from a table which had 25 fields/columns and he selected everything, then he will pay a price in the form a degraded performance of this application. The aim of this experiment is to measure how much are we loosing.

Here is the output:

Num of Records Num of columns in the table time(ms) to select all the columns using select * time(ms) to select one column, using select columnname
100000 1 0.0026185035705566 0.0023678302764892
100000 2 0.0031496906280517 0.0029053878784179
100000 10 0.0050011997222900 0.0029400386810302
100000 25 0.0053365493774414 0.0028691490173339
100000 50 0.0080362849426269 0.0031750718688964

select 1 field vs select all fields

Conclusion:

It is always good to select just the number of records that we require in our code/application. If we have ‘n’ fields in our table and if we have to select ‘m’ fields where m < n then do not use ‘select *’ as our experiments have shown that selecting all the records when it is not required degrades performance. But, if we are to choose all the ‘n’ fields of a table then whether to use ‘select *’ is a matter of choice.

So, till next, Happy PHPing

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: