Speeding up Select Query

Question

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.'

Answer 1

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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

Answer 2

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.