Page 1 of 1

Fast Word Capping - MySQL

Posted: Fri Feb 05, 2010 9:23 am
by JNettles
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?

Code: Select all

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 ;

Re: Fast Word Capping - MySQL

Posted: Fri Feb 05, 2010 12:02 pm
by pickle
Use the built-in PHP functions strtolower(), ucfirst(), and ucwords()

Re: Fast Word Capping - MySQL

Posted: Fri Feb 05, 2010 12:41 pm
by JNettles
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?

Re: Fast Word Capping - MySQL

Posted: Fri Feb 05, 2010 1:09 pm
by AbraCadaver
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.

Re: Fast Word Capping - MySQL

Posted: Fri Feb 05, 2010 1:33 pm
by pickle
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.

Re: Fast Word Capping - MySQL

Posted: Fri Feb 05, 2010 1:51 pm
by JNettles
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.