0

I have a table with thousands of links (some very long) and they store corresponding MD5 for quicker lookup.

Query 1 using MD5

SELECT dest FROM {affiliate_links_link} WHERE lid = :lid

Query 2 using sometimes a very long URL string

SELECT lid, dest, rebuild FROM {affiliate_links_link}
WHERE source = :source

Now you would think query 1 should be efficient but it's on average 200 times slower than query 2.

What am I missing?

Output for Show Create Table

SHOW CREATE TABLE affiliate_links_link;
affiliate_links_link | CREATE TABLE `affiliate_links_link` (
  `lid` char(16) NOT NULL DEFAULT '',
  `accid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Account ID from affiliate_links_account.accid.',
  `source` varchar(5000) DEFAULT NULL,
  `dest` varchar(5000) DEFAULT NULL,
  `rebuild` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Whether the account link should be rebuilt.',
  `count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Click count for cloaked link.',
  `nid` varchar(255) DEFAULT NULL COMMENT 'Node ID',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'creation and upadtion time of source/dest link',
  `cid` char(32) DEFAULT NULL,
  PRIMARY KEY (`lid`),
  KEY `affiliate_urls` (`source`(1024)),
  KEY `lid` (`lid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Affiliate links link conversion table.' |

Explain for Query 1:

+----+-------------+----------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table                | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | affiliate_links_link | const | PRIMARY,lid   | PRIMARY | 48      | const |    1 | NULL  |
+----+-------------+----------------------+-------+---------------+---------+---------+-------+------+-------+

Explain for Query 2:

 +----+-------------+----------------------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table                | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+----------------------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | affiliate_links_link | ref  | affiliate_urls | affiliate_urls | 3075    | const |    1 | Using where |
+----+-------------+----------------------+------+----------------+----------------+---------+-------+------+-------------+

Interestingly, every time I run Query 2, I also get a ERROR 2006 (HY000): MySQL server has gone away

2
  • Please post execution plan for both queries . Jun 5, 2023 at 7:22
  • thanks @ErgestBasha - added more detail. I am now getting ERROR 2006 (HY000): MySQL server has gone away error.
    – JM John
    Jun 5, 2023 at 7:44

1 Answer 1

0
  • MD5 generates a 32-character hex string. This will not fit in CHAR(16).
  • There is no need for utf8 on md5.
  • CHAR(16) CHARACTER SET utf8 takes 48 characters. (See Explain) Suggest: CHARACTER SET ascii COLLATION ascii_general_ci.
  • UNHEX(MD5(...)) could be put into a BINARY(16) column, taking 15 bytes
  • A PRIMARY KEY is an index, so there is no need to also have KEY(lid)

Unfortunately, none of the above, nor the Explains, explain the 200x you are seeing.

What version of MySQL? There may have been changes in "prefix indexing" to fix some of the awful performance of the past.

2
  • Thanks - I truncate the MD5 into 16 characters to make the query efficient. What collation would you recommend?
    – JM John
    Jun 6, 2023 at 0:22
  • Mysql 5.6 - I actually downgraded from current MariaDB and it has generally improved performance
    – JM John
    Jun 6, 2023 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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