1

I have the following query which needs to run up to 40-50 times on some pages.

SELECT lid, dest, rebuild FROM {store_links_link}
WHERE source = :url;

Source column is URLs and the problem is that some values of the urls/source are really long and it incredibly slows down the query.

I know that it should be fixed on the application level but what changes can be made in Mysql configuration to speed up this query?

store_links_link table has over 50,000 links and some can be quite long. I have query cache enabled which helps to some extent. Previously, the application ran on MySql 5.6 which was able to cope well but Mariadb 10 doesn't cope at all.

Output for SHOW CREATE TABLE:

CREATE TABLE store_links_link
  (
  lid char(32) NOT NULL COMMENT 'Link ID using MD5, also act as cloaked link.',
  accid int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Account ID from affiliate_links_account.accid.',
SOURCE varchar(5000) DEFAULT NULL,
  dest text NOT NULL COMMENT 'Converted link URL with ID info.',
  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',
  PRIMARY KEY (lid),
  KEY affiliate_urls (source(768))
  ) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='URL link conversion table.'
3
  • Please provide SHOW CREATE TABLE. How long is "really long"? Are you using VARCHAR? Or TEXT? Is url indexed?
    – Rick James
    May 6, 2023 at 14:30
  • Welcome to SO, please edit questions with addition details. As you can see the comment space isn't sufficient for this and is hard to read. I can't see an index on url. Consider how you'd restructure data so that this isn't run 40-50 times per page.
    – danblack
    May 6, 2023 at 23:20
  • @danblack Thanks - edited the question with details.
    – JM John
    May 7, 2023 at 0:07

2 Answers 2

0
  1. utf8mb4 -> latin1

It seems all the char/varchar types on the table aren't really utf8mb4, as urls, md5 and possible nid (do check) are just latin1.

Changing to latin1 will reduce the size of these stored, and hence require less IO to read the index to find this item.

  1. Partial index

Partial indexes should be avoided if possible. After changing to latin1 the maximum length is 3072. Is it possible to reduce the length of source to this?

Alternately, to reduce the index size, can you store a sha1 of the source, index this as a separate column and do a lookup of this (by doing sha1 in the application)? This will further reduce the index size.

  1. text inefficiencies

Text (and blob) aren't stored with the primary key and require and extra lookup internally to retrieve the url. Change dest to a varchar to store it inline.

  1. primary key

Do you do more lookups on lid or source? The most common one should be the primary key as primary key lookups are faster. Use the sha1 of the source as a primary key if you've change this in #2.

  1. InnoDB

Its quite possible that previously on MySQL-5.6 the table was MyISAM which can get considerable gain with the OS file cache. Changing to InnoDB, particularly with MariaDB-10.6+, can require explicitly setting an innodb_buffer_pool_size. A good starting point is 80% of available RAM.

  1. Reduce number of queries

If you are needing 40-50 queries of these per page it will take time. Even on a very well configured 1ms response time this is ~50ms of just retrieval time.

Consider a way that a single query can retrieve all the URLs needed at once. Ensure this is indexed correctly too.

4
  • Thanks - some really good points. You are right about table being MYISAM previously.
    – JM John
    May 8, 2023 at 10:09
  • I converted to utf8_general_ci on the table and page generation time went down by 500ms. Do you think converting to MyISAM would help any further?
    – JM John
    May 9, 2023 at 1:12
  • InnoDb should be comparable with sufficient innodb_buffer_pool_size to cover at least most of the indexes used. I expect with the current table size this is frequently updated, and as such, MyISAM and Aria would incur a table lock on updated. Also MyISAM lacks crash safety so I wouldn't recommend it.
    – danblack
    May 9, 2023 at 2:36
  • Thanks - I converted all tables to utf8_general_ci and overall site is 200ms faster as per New Relic. I think utf8mb4_general_ci (previously used) isn't great at dealing with large indices.
    – JM John
    May 10, 2023 at 3:04
1

you can try to add an index on reverse(source(32)), and change the query to

SELECT lid, dest, rebuild FROM {store_links_link}
WHERE source = reverse(:url);

768 is too large for an index key, which will make the level of index tree deep and comparison calculation time-consuming; but a small number will reduce the distinctness of the index key. So creating an index on the reverse value of source column with comparable small value of length should be helpful.

2

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.