CODEWITHSUNDEEP
phpmysql
user593858
user593858

Reputation: 1

Capitalization of First Name and Last Name at Database end, while fetching from database or via PHP Functions after fetchin it from database?

Which one is the faster approach:

a) fetch first name and last name from database after capitalizing in required format at database end using database functions:

SELECT CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2)), ' ', UPPER(SUBSTRING(last_name, 1, 1)), LOWER(SUBSTRING(last_name, 2))) AS user_name FROM emp;

<?php
$name = $user_name; // Robin Hood

or

b) fetch first name and last name from database, and then capitalize that using PHP functions.

SELECT first_name, last_name FROM emp;

<?php
$name = ucfirst($first_name).' '.ucfirst($last_name); // Robin Hood

Here are the time measurement, if I run both method in a for loop for 5000 times, just for the test of speeds.

(a)WITH DB Start Time = 1298468915

End Time = 1298468922

Diff = 7

(b)WITH PHP Start Time = 1298468922

End Time = 1298468930

Diff = 8

And here are more,

(a)WITH DB Start Time = 1298469109

End Time = 1298469115

Diff = 6

(b)WITH PHP Start Time = 1298469115

End Time = 1298469122

Diff = 7

And more,

(a)WITH DB Start Time = 1298469293

End Time = 1298469300

Diff = 7

(b)WITH PHP Start Time = 1298469300

End Time = 1298469307

Diff = 7

Upvotes: 0

Views: 335

Answers (2)

Andrew
Andrew

Reputation: 5213

First of all, I'm assuming you aren't doing this for performance, because this is the worst kind of optimization - the time it takes to even consider which is faster is longer than all of the cycles the faster one, whichever it is, would ever save, ever.

That said, it appears to me that the PHP method, with its single built-in function call, is probably superior to the MySQL method, and has the benefit of providing a much cleaner query as well.

Whatever you do, try to be consistent throughout your application.

Upvotes: 1

Alessandro
Alessandro

Reputation: 1336

Since PHP provides a single function to do what you need and MySQL requires multiple function calls, I'd say that the PHP way is "faster". However, that eventual gain is so little that you couldn't even reliably benchmark it. I suggest that you focus on other things in your code, database schema, DBMS configuration etc. that have much heavier impact on the performance of your application.

Upvotes: 0

Related Questions