Fast Word Capping - MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JNettles
Forum Contributor
Posts: 228
Joined: Mon Oct 05, 2009 4:09 pm

Fast Word Capping - MySQL

Post 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 ;
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Fast Word Capping - MySQL

Post by pickle »

Use the built-in PHP functions strtolower(), ucfirst(), and ucwords()
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
JNettles
Forum Contributor
Posts: 228
Joined: Mon Oct 05, 2009 4:09 pm

Re: Fast Word Capping - MySQL

Post 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?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Fast Word Capping - MySQL

Post 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.
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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Fast Word Capping - MySQL

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
JNettles
Forum Contributor
Posts: 228
Joined: Mon Oct 05, 2009 4:09 pm

Re: Fast Word Capping - MySQL

Post 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.
Post Reply