2

Evening all, I'm having a bit of trouble with a MySQL table full of vocabulary words today. I need to be able to quickly retrieve words with the same arrangement of consonants and vowels, so my thinking is something structured along these lines with about 300k rows:

CREATE TABLE words(
 `value` VARCHAR(100),
 `pattern` VARCHAR(100)
);

To populate a small fraction of the contents:

INSERT INTO words(`value`) VALUES
  ('aardvark'),
  ('aardvarks'),
  ('aardwolf'),
  ('aardwolves'),
  #...
  ('mortal'),
  ('mortals'),
  #...
  ('posted'),
  ('posteen'),
  #...
  ('zymotoxic'),
  ('zymurgies');

The following query should return "mortal" and "posted":

SELECT `value` FROM words WHERE `pattern` = '101101';

because '1' represents a consonant and '0' a vowel. I'm aware that MySQL doesn't have a regexp_replace equivalent, so I'm wondering what's the best way to populate the pattern field? I'm assuming it's not the deplorably slow:

UPDATE words SET `pattern` = REPLACE(`value`, 'a', '0');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'b', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'c', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'd', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'e', '0');
#...
UPDATE words SET `pattern` = REPLACE(`pattern`, 'z', '1');

Currently trying a stored procedure with a cursor looping through the words table and updating each pattern one by one but, perhaps unsurprisingly, that's no quicker. Am I missing something really obvious?

Thanks in advance for any help you're able to give me.

Edit: added the stored procedure approach. This is quicker but not by much (0.7 seconds per thousand rows, which adds up) but it gets the job done.

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `set_word_pattern`()
BEGIN
  DECLARE l_last_row INT DEFAULT 0;
  DECLARE temp_word VARCHAR(100);
  #DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE c_traverse_words CURSOR FOR SELECT `value` FROM words WHERE pattern = "" LIMIT 20000;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row = 1;

  OPEN c_traverse_words;
  cursor_loop: LOOP
    FETCH c_traverse_words INTO temp_word;
    IF l_last_row = 1 THEN
      LEAVE cursor_loop;
    END IF;
    SET temp_pattern_word = get_pattern(temp_word);
    UPDATE words SET `pattern` = temp_pattern_word WHERE `value` = temp_word;
  END LOOP cursor_loop;
  CLOSE c_traverse_words;

  SELECT "Done"; #temp_pattern_word;
END

and the function it calls:

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `get_pattern`(new_word VARCHAR(100)) RETURNS varchar(100) CHARSET utf8
BEGIN
  DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE i INT;

  SET temp_pattern_word = "";
  SET i = 1;

  WHILE(i <= CHAR_LENGTH(new_word)) DO
    SET temp_char = MID(new_word, i, 1);

    IF temp_char IN ("a", "e", "i", "o", "u") THEN
      SET temp_pattern_word = CONCAT(temp_pattern_word, "0");
    ELSE
      SET temp_pattern_word = CONCAT(temp_pattern_word, "1");
    END IF;
    SET i = i + 1;
  END WHILE;
RETURN temp_pattern_word;
END
  • Maybe you could just create a trigger or function, these may contain loops. aeiouy should be enough as anything else may be replaced with 1. – jkavalik Jun 9 '16 at 20:14
  • Thanks, @jkavalik - I'm amending my stored proc version with this in mind. Will edit the original post and show that too. – Samuel Barnett Jun 9 '16 at 20:24
  • 1
    Just as a matter of interest, why are you doing this? – Vérace Jun 9 '16 at 21:09
  • @SamuelBarnett could you try to just run the update and call the function inside SET? UPDATE words SET pattern = get_pattern(value) - you can run it with limit too (and use where pattern is null or similar to just update the rows not yet processed). That way there should be less overhead and just one query to execute instead of many. – jkavalik Jun 10 '16 at 17:09
0

To do it in one pass:

UPDATE ...
    SET pattern = 
        REPLACE(
        REPLACE(
        ...
        REPLACE(value
                'a', '0')
                'b', '1')
                ...
                'z', '1');

Doesn't your table need

PRIMARY KEY(value),
INDEX(pattern)

There is probably a way to convert those 0s and 1s into a bit string and store it into a BIGINT UNSIGNED for slightly faster lookup and less disk space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.