Hi,

I wonder if someone can help me with the following simple(?) MySQL problem.

I have a table 'action' with about 180,000 web server requests records running under MySQL Ver 14.7 Distrib 4.1.12 (Fedora Core 4)

mysql>desc action +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | host_ip | varchar(16) | | | | | | file | varchar(255) | | | | | | querystring | varchar(255) | YES | | NULL | | | timestamp | datetime | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)

From this table, I must delete all records associated with host_ips that occur only once in the table (all unique host_ips).

I have tried the following approaches; all of which hang the server and corrupt the table:

Method 1: First create a 'totals' table that holds for each host_ip the number of occurrences in the 'action' table:

mysql> create table totals as select host_ip, count(*) as hits from action group by host_ip order by hits;

Next, combine the tables in a query (a 'select' for now, but a 'delete' eventually):

mysql> select from action where host_ip in ( select host_ip from totals where hits = 1 );

Method 2: use an explicit join:

mysql> select host_ip from action, totals where action.host_ip = totals.host_ip and totals.hits = 1;

Method 3: don't use the 'totals' table at all:

mysql> select host_ip from action group by host_id having count(*) = 1;

As mentioned, none of these work and all of these hang the server and break the database.

How do I do this?