CODEWITHSUNDEEP
phpmysqlsqlperformanceconcatenation
LibertyPaul
LibertyPaul

Reputation: 1167

MySQL CONCAT performance vs. PHP concatenation performance

I have an SQL SELECT request with 2 fields:

SELECT `a`, `b` FROM `t`

(Both a and b are VARCHAR(255))

I have to concatenate them this way: a (b)

I can do it on a DB level:

SELECT CONCAT(`a`, ' (', `b`, ')') FROM `t`

Or in the PHP script:

$result = $resp['a'].' ('.$resp['b'].')';

Which way is more correct and efficient?

P.S. I can test it on my own computer, but:

  1. Results on different hardware may differ;
  2. I want to know pros and cons of both ways so I can apply best practice in my projects.

Upvotes: 1

Views: 1562

Answers (1)

Rick James
Rick James

Reputation: 142228

If the client and server are on different machines, then you should probably choose the machine with lower CPU usage.

The cost of the concat (either MySQL or PHP) is minuscule compared to the rest of the effort. I would expect a benchmark to come back with less than 1% difference. My Rule of Thumb: Don't worry about an optimization that won't give at least 10%. Focus on optimizations that give more benefit.

The number of bytes sent in the resultset is about the same in either case, so this is not a factor.

Does the answer for concat apply to other situations? Maybe, maybe not. Another example is "Should I sort using MySQL's ORDER BY or PHP's sort()? Again, this feels like another "toss a coin" question. But... There could be a good reason for doing it in SQL: Suppose you are ordering strings and you are using the collation utf8mb4_unicode_520_ci. That is trivial to specify in MySQL. But for PHP, you would have to load some library and figure out how to use it. (Even so, the performance might be very similar.)

Upvotes: 6

Related Questions