MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.13


CREATE TABLE `new_random` (
  `new_domain` varchar(500) NOT NULL,
  PRIMARY KEY (`new_domain`))
INSERT INTO new_random VALUES ('http://a.b.c'),('http://d.e.f')
ALTER TABLE new_random ADD KEY(substring_index(`new_domain`,'http://',-1))
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`new_domain`,'http://',-1))' at line 1

ALTER TABLE new_random ADD no_https VARCHAR(500) AS (substring_index(`new_domain`,'http://',-1)), ADD KEY(no_https)
ALTER TABLE new_random ADD rev_domain VARCHAR(500) AS (REVERSE(no_https)), ADD KEY(rev_domain)
SELECT * FROM new_random


new_domain
no_https
rev_domain


http://a.b.c
a.b.c
c.b.a

http://d.e.f
d.e.f
f.e.d


SHOW CREATE TABLE new_random


Table
Create Table


new_random
CREATE TABLE `new_random` (
  `new_domain` varchar(500) NOT NULL,
  `no_https` varchar(500) GENERATED ALWAYS AS (substring_index(`new_domain`,_utf8mb4'http://',-(1))) VIRTUAL,
  `rev_domain` varchar(500) GENERATED ALWAYS AS (reverse(`no_https`)) VIRTUAL,
  PRIMARY KEY (`new_domain`),
  KEY `no_https` (`no_https`),
  KEY `rev_domain` (`rev_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


EXPLAIN SELECT new_domain FROM new_random WHERE rev_domain LIKE 'c.b.%'


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
new_random
null
range
rev_domain
rev_domain
2003
null
1
100.00
Using where; Using index