I've been handed this MySQL database to create a listing. The problem is, every single entry is in uppercase. Everything. This wouldn't be a problem except that the client wants the text to appear "regular" with the first word in each sentence capitalized and the rest lowercase. This is a pretty intensive query for a simple listing - 3 full seconds to return the 10 entries required.
Capping only the first letter isn't that big of a deal. Unfortunately there are several fields that are "title" fields, where each word needs to be capitalized. I wrote the following MySQL function to handle it but running it stretches my query out to 7 - 8 seconds which I'm guessing is going to be an unacceptable amount of time. Anybody got any suggestions on how I can speed this up, either through code optimization or a different solution?
DELIMITER $$
DROP FUNCTION IF EXISTS `CAP_FIRST` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `CAP_FIRST`(input VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE len INT;
DECLARE i INT;
SET len = CHAR_LENGTH(input);
SET input = lower(input);
SET i = 0;
WHILE (i < len) DO
IF (MID(input,i,1) = ' ' OR i = 0) THEN
IF (i < len) THEN
SET input = CONCAT(
LEFT(input,i),
UPPER(MID(input,i + 1,1)),
RIGHT(input,len - i - 1)
);
END IF;
END IF;
SET i = i + 1;
END WHILE;
RETURN input;
END $$
DELIMITER ;
I would but the client tells me that the listing needs to be able to scale out to 500 displayed records in the future (yes, I've told them how stupid this is). I'm just a little wary about performance if I'm potentially performing 5000 string operations per search.
Its a dedicated database sever, so which do I hit with this operation? The web server and transform with PHP or have the database do it all and use the SQL function?
Why not use the result of your function to update the rows? You run this once and all your data is stored properly and you don't have to worry about it again.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
AbraCadaver wrote:Why not use the result of your function to update the rows? You run this once and all your data is stored properly and you don't have to worry about it again.
Good thinking.
If that's not possible though - I'd go with the PHP solution. You said your MySQL solution takes 3 seconds for 10 records. There's no way the PHP solution will take that long.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Why not use the result of your function to update the rows?
That was my thought - I wanted to just run this against the database and be done with the whole situation but apparently its their data entry standard to type everything in all caps. All 6.7 million records are in all caps. The data department absolutely refuses to change their "time-tested, tried and true standards".
I suppose I'll go with the PHP string operations - at least they don't plan on offering the scale-up to 500 results for another year or so. They can deal with it then.