Is php or sql faster for basic operations

Question

I wanted to know that when given the option, is it faster to do processing with php or mysql? For example, lets say that I want to concatenate two values from two columns should I do

SELECT col1,col2 FROM table WHERE conditions

And then concatenate in php

or should I do

SELECT CONCAT(col1,col2) as concatenated FROM table WHERE conditions

and then not have to concatenate?

Another example would be limiting the size of returned strings which can be done in sql with LEFT or in php using substr

If they are the same speed then which is the prefered method?

I am using recent version of xampp on a windows 7 machine.

Answer 1

For the most part, performing batch operations on your data is going to be faster at the database level. The engines are optimized for working with sets of data like this, and they can concurrently process and transfer data (for very large result sets).

Answer 2

1. When in doubt, benchmark it out
2. Based on your example, it's faster to do it in one step in the database vs 2 steps (database then php)

It'd be different if you were comparing data stored in a database compared to data stored elsewhere - perhaps already in memory. PHP may have some functions that could make it equal, but generally databases are optimized to work with specific data types and process only a limited set of instructions, whereas PHP may require more resources, since it's a multi-functional interpreter.

A larger impact would be on the type of hardware used; and if it's only a small set of data, it probably wouldn't matter either way.