SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    110
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    How to set a variable if a row already exists?

    I have a table like so:
    Code SQL:
    CREATE TABLE `shortURL` (
     `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
     `sURL` VARCHAR(5) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
     `lURL` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `lURL` (`lURL`),
     KEY `sURL` (`sURL`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    What I want to do is to try inserting a new row with a value into the lURL field, but if the record already exists, then set a variable @sURL to the value of the sURL column for the record in question.

    Currently I'm doing this like so:
    Code MySQL:
    INSERT INTO shortURL VALUES(NULL, '', lURL)
            #If the long URL already exists, then set @sURL to the short URL value
            ON DUPLICATE KEY UPDATE
            id = IF( (@sURL := sURL), id, id);

    Is there a better way of setting the @sURL to the value of sURL if the record already exists?

    The full procedure looks like this:
    Code SQL:
    CREATE FUNCTION makesURL (lURL CHAR(255))
    RETURNS CHAR(5) DETERMINISTIC
    BEGIN
        #First try AND INSERT the long URL
        INSERT INTO shortURL VALUES(NULL, '', lURL)
            #If the long URL already EXISTS, THEN SET @sURL TO the short URL VALUE
            ON DUPLICATE KEY UPDATE
            id = IF( (@sURL := sURL), id, id);
        #If the record didn't already exist, so we've just inserted a NEW record, CREATE the short URL AND UPDATE the record
        IF @sURL IS NULL THEN
            SET @sURL = strFromNum(LAST_INSERT_ID());
            UPDATE shortURL SET shortURL.sURL = @sURL
                WHERE shortURL.id = LAST_INSERT_ID()
                LIMIT 1;
        END IF;
        RETURN @sURL;
    END//

    P.S. Most records will be new, so trying to insert the record first rather than trying to select it first is much faster.

    Thanks

    Dave

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    110
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Never mind, seems that actually I do have to check if the value already exists before trying to insert it, as the InnoDB storage engine allocates auto increment values before the actual INSERT. So if an insert fails because of a duplicate value, the auto increment value will be still be increased.

    It seems that checking if a value exists before trying to insert it doesn't make as much of a difference to the speed as I had thought either.

    The function I posted above (should actually have SET@sURL = NULL; at the top of it) benchmarked the following starting with a clean table and then the benchmark being run 4 times in succession:
    Code MySQL:
    SELECT BENCHMARK(10000, ( SELECT makesURL( SUBSTRING( MD5(RAND()),1,4 ) ) )); #1.99 #1.78 #1.67 #1.61
    Whilst checking if the value exists firsts and benchmarking the same way starting with a clean table gives:
    Code MySQL:
    SELECT BENCHMARK(10000, ( SELECT makesURL4( SUBSTRING( MD5(RAND()),1,4 ) ) )); #2.17 #1.80 #1.61 #1.48
    for the following function:
    Code MySQL:
    CREATE FUNCTION makesURL4 (lURL CHAR(255))
    RETURNS CHAR(5) DETERMINISTIC
    BEGIN
        DECLARE sURL CHAR(5);
        DECLARE CONTINUE HANDLER FOR NOT FOUND 
    	BEGIN
    		#If the record doesn't already exist, insert a new record, create the short URL and update the record
    		INSERT INTO shortURL VALUES(NULL, '', lURL);
            	SET sURL = strFromNum(LAST_INSERT_ID());
            	UPDATE shortURL SET shortURL.sURL = sURL
                	WHERE shortURL.id = LAST_INSERT_ID()
                	LIMIT 1;
    	END;
        #First try and get the sURL based on the long URL
        SELECT shortURL.sURL INTO sURL FROM shortURL WHERE shortURL.lURL = lURL LIMIT 1;
        RETURN sURL;
    END//

    Dave


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •