String performacne: PHP vs MySQL

Question

Are there any performance concerns of note when using MySQL's CONCAT() function in a select query? Is it faster/slower/negligible to do a simple select, and format strings for a view using PHP after the result set from the database is returned? Or is a more complicated SQL query with multiple calls to CONCAT() that returns a string already formatted for the view a better approach?

ie is this:

select CONCAT(lastname, ', ', firstname) from people;

Faster/Slower/No difference from this:

<?php
    $query = 'Select lastname, firstname from people';
    ...

    $name = $data['lastname'] . ', ' . $data['firstname']; //OR
    $name = sprintf("%s, %s", $data['lastname'], $data['firstname']);
?>

before opening such question do your benchmark, it would be pretty easy — dynamic, Mar 07 '12 at 19:23
Why do you think it's even worth spending time to find that out? — zerkms, Mar 07 '12 at 19:24
No matter which is faster, consider you can very easily have more than one machine running PHP, but not without quite a lot more considerable effort have more than one MySQL box. — Joachim Isaksson, Mar 07 '12 at 19:25
what about design? What happens if later on you decide you need them separated? — miki, Mar 07 '12 at 19:26

score 2 · Accepted Answer · answered Mar 07 '12 at 19:26

2

You're better off in almost all cases by doing filtering and data massaging with the SQL engine versus on the web server.

answered Mar 07 '12 at 19:26

LJ Wilson

14,445
5
38
62

I know this is old, but why? Can you provide some link? – Francisco Presencia Nov 17 '12 at 17:05
1

Well, some searching will provide sources, but the main difference is that the SQL engine doesn't have to instantiate objects and already knows the schema of the data. If you do this on the web-server (using PHP, C# or whatever), you will lose these benefits. While realistically, this wouldn't even be noticeable unless you were doing lots of this, it is still best practice to do this kind of work in SQL – LJ Wilson Nov 17 '12 at 22:02

score 1 · Answer 2 · answered Mar 07 '12 at 19:25

Unless you are planning to do hundreds of thousands such operations at once, it will not matter where you do the string concatenation from a performance point of view. The possible time savings will be so minuscule, they will probably not even be measurable.

String performacne: PHP vs MySQL

2 Answers2