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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
