Page 1 of 1
Help with a delete query
Posted: Wed Jul 02, 2008 10:04 pm
by s.dot
I have a table.

Each user should have 30 rows in this table, corresponding to the last 30 days (one new row is inserted per day).
I want to delete all the rows (for each user) that are older than the 30 newest records.
For example there are three users bob, jack, and jim. They each have 100 records. I want to delete the all except the newest 30 records.
I could effectively do something like this for each user:
Code: Select all
DELETE FROM `table` WHERE `id` NOT IN (SELECT `id` FROM `table` WHERE `username` = 'bob' ORDER BY `id` DESC LIMIT 0, 30) AND `username` = 'bob'
However, I don't want to loop through thousands of usernames doing thousands of queries. So I won't know the usernames to put in the where clause of the subquery.
I was thinking I could do a group by `username` somewhere, but I'm not sure how to do it using subqueries.
Is this possible in one query?
Re: Help with a delete query
Posted: Wed Jul 02, 2008 11:46 pm
by Kieran Huggins
not sure if it would work, but my instinct would be along the lines of:
Code: Select all
DELETE FROM TABLE GROUP BY username ORDER BY id ASC LIMIT 30, 10000
but it seems to me that keeping the data and modifying your select to just show the most current would be a better practice. SQL data is relatively small and storage is cheap.
Re: Help with a delete query
Posted: Thu Jul 03, 2008 2:16 am
by s.dot
Kieran Huggins wrote:not sure if it would work, but my instinct would be along the lines of:
Code: Select all
DELETE FROM TABLE GROUP BY username ORDER BY id ASC LIMIT 30, 10000
but it seems to me that keeping the data and modifying your select to just show the most current would be a better practice. SQL data is relatively small and storage is cheap.
Yeah, that's what I've been doing. I will try playing around with your query and see if I can get it to work. My line of thinking is that the extra records are 'baggage'. I keep 30 days history and display it in a graph. I don't need more than that. And I was thinking that since now there's getting around 400,000 and growing rows that the queries may be getting slower. Will have to run some benchmarks to see.
Re: Help with a delete query
Posted: Sat Jul 05, 2008 1:14 am
by Ollie Saunders
This is premature optimization. Also this is undoubtedly way easier to do in two queries.
The cloest thing I could come up with is this:
Code: Select all
mysql> delete from `a` where id in (select 10); -- works! :-)
Query OK, 1 row affected (0.00 sec)
mysql> delete from `a` where `id` in (select `id` LIMIT 30,999999999999); -- doesn't work :-(
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.51a |
+-----------+
1 row in set (0.00 sec)
Even if it did work I don't like the 99999 thing. But there's not really getting round that either:
MySQL Manual wrote: To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:
Code: Select all
SELECT * FROM tbl LIMIT 95,18446744073709551615;
Which strikes me at pretty ridiculous.
Here's another query that doesn't work:
Code: Select all
SELECT * FROM a LIMIT 30,(SELECT count(*) - 30)