DELETE with a percentage on total count

Question

Let's say I want to delete 10% of rows, is there a query to do this?

Something like:

DELETE FROM tbl WHERE conditions LIMIT (SELECT COUNT(*) FROM tbl WHERE conditions) * 0.1

Answer 1

If you only need roughly 10% of rows, in no particular order, this should do the trick:

DELETE FROM tbl WHERE RAND() <= 0.1

However, I don't recommend using it on very large data sets due to the overhead of generating random numbers.

Answer 2

I would simply return the total amount of filtered rows, calculate through php and use that value as a limit in my DELETE query.

$query = mysql_query("SELECT COUNT(*) FROM tbl WHERE conditions");
$int = reset(mysql_fetch_array($query));
$int = round($int * 0.1);

mysql_query("DELETE FROM tbl WHERE conditions LIMIT {$int}");

I'm not sure if DELETE allows an advanced query such as this one:

DELETE FROM (   SELECT h2.id
                FROM (  SELECT COUNT(*) AS total
                        FROM tbl
                        WHERE conditions) AS h
                JOIN (  SELECT *, @rownum := @rownum + 1 AS rownum
                        FROM tbl, (SELECT @rownum := 0) AS vars
                        WHERE conditions) AS h2
                ON '1'
                WHERE rownum < total * 0.1) AS h3