List:General Discussion« Previous MessageNext Message »
From:Eric Anderson Date:December 19 2002 9:31pm
Subject:Excluding records?
View as plain text  
If I've got the following two tables:

CREATE TABLE password_log (
  time_stamp int(11) unsigned NOT NULL default '0',
  remote_host char(15) NOT NULL default '',
  remote_user char(50) NOT NULL default '',
  status smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (remote_host,remote_user,status),
  KEY time_stamp (time_stamp),
  KEY remote_user (remote_user),
  KEY status (status)
) TYPE=MyISAM;

CREATE TABLE exclude_log (
  ip_block char(15) NOT NULL default '',
  PRIMARY KEY  (ip_block)
) TYPE=MyISAM;

#
# Dumping data for table 'exclude_log'
#

INSERT INTO exclude_log VALUES ('152.163.188');
INSERT INTO exclude_log VALUES ('152.163.189');
INSERT INTO exclude_log VALUES ('152.163.206');
INSERT INTO exclude_log VALUES ('152.163.207');
INSERT INTO exclude_log VALUES ('195.93.64');
INSERT INTO exclude_log VALUES ('195.93.65');
INSERT INTO exclude_log VALUES ('195.93.66');
INSERT INTO exclude_log VALUES ('195.93.72');
INSERT INTO exclude_log VALUES ('195.93.73');
INSERT INTO exclude_log VALUES ('195.93.74');
INSERT INTO exclude_log VALUES ('205.188.208');
INSERT INTO exclude_log VALUES ('205.188.209');

And I want a list of everything in the 'password_log' table that doesn't
match up with any entry in the 'exclude_log' table, something along the
lines of:

select remote_user, substring_index(remote_host,'.',3) As ip_subnet from
password_log, exclude_log where remote_user != '-' and status=200 and
substring_index(remote_host,'.',3) != exclude_log.ip_block group by
ip_subnet order by remote_user, ip_subnet\g

+---------------------------+-------------+
| remote_user               | ip_subnet   |
+---------------------------+-------------+
| adamvernau                | 207.79.8    |
| amos                      | 24.53.232   |
| badmilk                   | 62.57.227   |
| barak@stripped          | 80.103.137  |
| beerbomb60                | 12.80.11    |
| BogusBob                  | 65.58.37    |
| brendenm123               | 172.190.203 |
| brize                     | 217.39.73   |
| bruneau                   | 195.242.80  |
| chicken                   | 24.101.127  |
| cracking                  | 213.122.143 |
| DanielNoble               | 172.151.183 |
| DESIO                     | 204.213.78  |
| diamond                   | 4.60.97     |
| dlgeo                     | 68.42.127   |
| ewing                     | 195.29.35   |
| fnadeau                   | 64.228.196  |
| frogman                   | 67.234.8    |
| fujerome                  | 156.143.132 |
| geno6969                  | 65.58.94    |
| gravy01                   | 81.86.119   |
| iftkharmaan               | 62.255.64   |
| jamesz                    | 204.186.14  |
| jaysap                    | 12.235.160  |
| karak                     | 80.63.120   |
| kevin                     | 152.163.188 |
| kevin                     | 152.163.189 |
| kevin                     | 152.163.201 |
| kjelljanssonx             | 213.66.154  |
| ksm70512                  | 172.195.152 |
| leinad                    | 210.120.128 |
| leinad                    | 66.68.138   |
| lemurs                    | 24.60.185   |
| leolebr                   | 81.48.138   |
| Malakon                   | 24.186.21   |
| martisr                   | 217.39.29   |
| matglew                   | 81.98.84    |
| mikeestela                | 129.106.169 |
| Mirhos                    | 80.11.19    |
| newyork                   | 62.134.74   |
| ordinary3                 | 12.37.234   |
| pcomdh                    | 212.185.249 |
| pp-hosereed               | 24.61.65    |
| pp-lobeneath              | 67.82.86    |
| prodrifter72              | 66.75.124   |
| RbrtMackay                | 81.77.108   |
| revrendpoe                | 65.43.0     |
| rockey                    | 62.64.135   |
| rockey                    | 80.225.68   |
| stwgolfer                 | 64.221.53   |
| thebear                   | 205.188.208 |
| thebear                   | 205.188.209 |
| tooms                     | 63.225.249  |
| ulyanov                   | 12.5.196    |
| WHATSUP                   | 172.173.81  |
| wolfgang-ch.kleine@stripped | 80.143.42   |
| xmartyx                   | 68.5.149    |
| xym180                    | 216.41.132  |
| zook10                    | 213.89.57   |
+---------------------------+-------------+
59 rows in set (0.03 sec)

As you can see, I still end up with records from the 'exclude_log'
table.. ?

Thread
Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
  • Re: Can MySQL handle 120 million records?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
    • Re: Can MySQL handle 120 million records?Michael She18 Dec
      • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
        • RE: Can MySQL handle 120 million records?Adam Nelson18 Dec
      • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
        • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
          • Re: Can MySQL handle 120 million records?Gerald Jensen18 Dec
            • Re: Can MySQL handle 120 million records?Aaron Clausen18 Dec
    • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
        • Database type questionRichard E. Perlotto II19 Dec
          • Re: Database type questionIikka Meriläinen19 Dec
      • Re[2]: Can MySQL handle 120 million records?Dyego Souza do Carmo19 Dec
    • RE: Can MySQL handle 120 million records?Wayne Lewis18 Dec
      • Excluding records?Eric Anderson19 Dec
        • Re: Excluding records?Adolfo Bello20 Dec
    • Re: Re[2]: Can MySQL handle 120 million records?Harald Fuchs19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
  • Re: Can MySQL handle 120 million records?Muruganandam18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
      • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
            • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
              • Re: Can MySQL handle 120 million records?Michael She18 Dec
                • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
            • Re: Can MySQL handle 120 million records?Michael Bacarella18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Can I build a web page to extract data?Gary Hostetler18 Dec
            • Re: Can MySQL handle 120 million records?Csongor Fagyal19 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
Re: Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?David T-G18 Dec
    • RE: Can MySQL handle 120 million records?JamesD18 Dec
      • Re: Can MySQL handle 120 million records?David T-G18 Dec
RE: Can MySQL handle 120 million records?Peter Vertes18 Dec
  • RE: Can MySQL handle 120 million records? - Impressive! How do you guys do that?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How doyou guys do that?R. Hannes Niedner18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How do youguys do that?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Jocelyn Fournier19 Dec
          • Thanks! Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too...Qunfeng Dong20 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Ryan Fox19 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Brent Baisley19 Dec
  • RE: Can MySQL handle 120 million records?JamesD19 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny21 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
        • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny22 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny23 Dec
            • DELETE with no WHERE clauseGordon23 Dec
              • Re: DELETE with no WHERE clauseDennis Salguero24 Dec
              • re: DELETE with no WHERE clauseVictoria Reznichenko24 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • RE: Can MySQL handle 120 million records?Dean Harding21 Dec
          • RE: Can MySQL handle 120 million records?JamesD21 Dec
RE: Can MySQL handle 120 million records?David Brodbeck18 Dec
RE: Can MySQL handle 120 million records?Greg_Cope18 Dec
Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
RE: Can MySQL handle 120 million records?RBRoa19 Dec
RE: Can MySQL handle 120 million records?Dana Diederich19 Dec
RE: Can MySQL handle 120 million records?John Griffin19 Dec
Re: Excluding records?King_Henree@yahoo.com20 Dec