Protip: When bean-counting a 100 million+ row table,

Protip:  When bean-counting a 100 million+ row table,

It is easier to make a summary table then sort its results than have MySQL do this:

select item, count(*) as num_deletions from DeletedCrap group by item order by num_deletitions


Do this instead:

create table report
(
def bigint not null default 0,
deletions bigint not null default 0,
primary key (def_id)
) engine = innodb;

insert into report (select item,1 from db.DeletedCrap) on duplicate key update deletions=deletions+1;

Select * from report order by deletions desc limit 1000;

(I've found that one finishes... the other doesn't)

P.S.

The other fastest way is to use something like PHP where array keys can be dynamically created, and increment dynamic array bean counters as the data streams in from the DB.  The above way while faster still incurs transaction overhead the PHP way does not.