Styjun

I am using mysql-workbench 6.3 on Ubuntu 18.04.

I have three tables created as follows:

CREATE TABLE `prefix_random` (
 `domain` varchar(500) NOT NULL,
 PRIMARY KEY (`domain`),
 UNIQUE KEY `domain_UNIQUE` (`domain`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Note: there are additional 32 fields but I do not query them, omitted for brevity.

Example:

domain
-----------------
sub.example.net

The second table:

CREATE TABLE `noprefix_random` (
 `domain` varchar(500) NOT NULL,
 PRIMARY KEY (`domain`),
 UNIQUE KEY `domain_UNIQUE` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Note: there are additional 32 fields but I do not query them, omitted for brevity.

Example:

domain
----------------------
example.net

The third table:

CREATE TABLE `new_random` (
 `new_domain` varchar(500) NOT NULL,
 PRIMARY KEY (`new_domain`),
 UNIQUE KEY `new_domain_UNIQUE` (`new_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Note: there are additional 3 fields but I do not query them, omitted for brevity.

Example:

new_domain
------------------------
http://sub.example.com

I want to make a query that identifies the shared name example.com in the three tables as follows:

Query:

SELECT `new_random`.`new_domain`,`prefix_random`.`domain`,`noprefix_random`.`domain`
FROM `myscheme`.`new_random`
JOIN `myscheme`.`prefix_random`
# the substring to extract the part: sub.example.com
ON substring_index(`new_random`.`new_domain`,'http://',-1) = `prefix_random`.`domain`
JOIN `myscheme`.`noprefix_random`
# by adding sub, it becomes: sub.example
ON CONCAT('sub.',`noprefix_random`.`domain`) = `new_domain`,`prefix_random`; 

The expected output is:

http://sub.example.com, sub.example.com, example.com

The query lasts forever. If I limit the output to small number using L

LIMIT 10;

I get results. The records number is not too larges. prefix_random contains 620062, noprefix_random contains , and the 62294, and the 588380 records.

What is the problem? Can you help me make the query run?

The best way to deal with domains is to use generated columns that has the reverse function on them and index this generated columns. This way a significant amount of queries can be WHERE domain LIKE CONCAT(reverse(const),'%') and use an index.

Removing http:// in a query is also an expensive way to do this. Use this in a generated function/index too.

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

 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

db<>fiddle here