0

On MySQL 5.7.34 I have this simple db structure. Important columns are domain name, domain total links in, but entries are duplicate (once for each found link from various sources), I need to select domain only once (link_to_domain) and sort it by link_to_domain_total_links_in DESC; Not sure how to render it here, here is the query:

CREATE TABLE IF NOT EXISTS `domain_to_domain_links` (
  `id` int(11) NOT NULL,
  `link_to_domain_hash` varchar(16) NOT NULL,
  `link_to_domain_total_links_in` int(11) NOT NULL DEFAULT '0',
  `link_to_domain` varchar(128) NOT NULL,
  `link_from_domain` varchar(128) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=270245 DEFAULT CHARSET=utf8;


INSERT INTO `domain_to_domain_links` (`id`, `link_to_domain_hash`, `link_to_domain_total_links_in`, `link_to_domain`, `link_from_domain`) VALUES
(1, 'c9b13094745bae79', 3, 'example.com', 'from-other-site1.com'),
(1, 'c9b13094745bae79', 3, 'example.com', 'from-other-site2.com'),
(1, 'c9b13094745bae79', 3, 'example.com', 'from-other-site3.com'),
(2, 'c43f16c897f72994', 2, 'foo.com', 'from-other-site4.com'),
(3, 'c43f16c897f72994', 2, 'foo.com', 'from-other-site5.com');

This returns exactly what I need but from what I understand it actually counts the entries (as links) at query time, I need it to get it from the link_to_domain_total_links_in so that it runs faster:

SELECT link_to_domain, COUNT(*) AS my_links_counter 
FROM domain_to_domain_links 
GROUP BY link_to_domain 
ORDER BY COUNT(*) DESC;

Also link_to_domain_hash is indexed, if it can take advantage of that when selecting it should be faster than link_to_domain, but this is not critical.

1 Answer 1

1

If I understand your table correctly, link_to_domain_total_links_in represents the number of rows that exist in the table for that particular link_to_domain.

Instead of COUNT() you can use the MAX() or MIN() aggregate function on the link_to_domain_total_links_in column (since it'll always be the same for any instance of link_to_domain).

But at that rate you can just add link_to_domain_total_links_in to the GROUP BY clause, and then you're allowed to SELECT and / or ORDER BY it.

But then you're not aggregating anything anymore and don't even need a GROUP BY clause, rather you can just use the DISTINCT keyword against your two columns like so:

SELECT DISTINCT 
    link_to_domain,
    link_to_domain_total_links_in AS my_links_counter 
FROM domain_to_domain_links 
ORDER BY link_to_domain_total_links_in DESC;
1
  • 1
    Thank you, that works well, I tried something similar but not sure what I was doing wrong. Looks simple now :)
    – adrianTNT
    Commented Jul 16, 2022 at 13:41

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.