MySQL Detecting cheaters

MySQL

I have This table :

    SET NAMES utf8;
SET time_zone = '+00:00';

CREATE TABLE `app_log` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `serial` int(11) NOT NULL,
  `dataEntryIP` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `dataEntryUA` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `domain` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `timeStamp` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `serial` (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=4328 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Sample of the possible data would be like :

INSERT INTO `app_log` (`ID`, `serial`, `dataEntryIP`, `dataEntryUA`, `domain`, `timeStamp`) VALUES
(6,33605,'2.5.29.18','Google Chrome','www.domain1.com','2017-08-18 18:44:23'),
(7,33606,'8.102.167.213','Google Chrome','domain1.com','2017-08-18 18:45:48'),
(8,33607,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 18:58:13'),
(9,33608,'8.24.10.14','Google Chrome','domain0.com','2017-08-18 18:58:13'),
(10,33609,'8.24.10.14','Google Chrome','domain1.com','2017-08-18 19:06:44'),
(11,33610,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 19:06:44'),
(12,33611,'7.10.14.11','Google Chrome','domain2.com','2017-08-18 19:12:48'),
(13,33612,'7.10.14.11','Google Chrome','www.domain2.com','2017-08-18 19:12:48'),
(14,33613,'9.45.21.18','Google Chrome','domain3.com','2017-08-18 19:18:10');

Now I am in need of selecting any IP which have used more than one domain, for this sample, it is : 8.24.10.14 because it have used both domains : domain0 and domain1

How would I do this ??

Thank you very much

Best Answer

You can use this query:

SELECT dataEntryIP FROM app_log
GROUP BY dataEntryIP
HAVING COUNT(*) > 1

Be careful above query doesn't use any index.

If your table will be grow, index the dataEntryIP column

ALTER TABLE `app_log`
ADD INDEX `dataEntryIP` (`dataEntryIP`);

Update 1

Oh sorry I did not understand your question.

I suggest before you insert the data into app_log remove www. prefix to unify domains (www.domain1.com is equal to domain1.com).

But for now I think it can help:

SELECT dataEntryIP from (
SELECT replace(domain, 'www.', '') as domain_without_www ,dataEntryIP from 
app_log
GROUP BY domain_without_www,dataEntryIP ) t1
GROUP BY dataEntryIP 
HAVING COUNT(*) > 1

But It is very slow, if your table is big.

if you can remove www. before insert to table, your query will be easier and faster.

Related Question