Consider the following table:
CREATE TABLE HOSTS(
hostname VARCHAR(100)
);
INSERT INTO HOSTS
VALUES
('SERVER-100'),
('SERVER-200'),
('TERMINAL1-100'),
('TERMINAL1-200'),
('TERMINAL2-200'),
('TERMINAL1-300'),
('TERMINAL2-300');
I would like a query that will identify the '300' component of the hostname field where there is no server for that site ID.
The query I have is:
SELECT DISTINCT substring_index(hostname,'-',-1) AS site
FROM `hosts`
WHERE
substring_index(hostname,'-',-1) IN
(
SELECT DISTINCT substring_index(hostname,'-',-1) FROM `hosts` WHERE hostname LIKE 'TERM%'
)
AND
substring_index(hostname,'-',-1) NOT IN
(
SELECT DISTINCT substring_index(hostname,'-',-1) FROM `hosts` WHERE hostname LIKE 'SERV%'
)
This works but is painfully slow in my production DB which has ~10000 rows.
Is there a more scalable approach to this problem?
Thanks,
Shawn