Save only 10 Records but delete all others by ID Huge Database

I need a solution to remove all records but save last 10 by ID_URL not BY UNIQUE ID (primary Key)..

MY Database Structure and Records View..

I need to s开发者_Go百科ave last 10 ITEM of EVERY URL_ID..

EDIT: Maybe most or are you confused what I am asking about.. actually I just want to save NEW 10 EVERY URL_ID records only..

   ID   IP             TIME(TIMESTAMP)   BROWSER                                                Visitor_URL     URL_ID
   11   xxx.xxx.xx.xx   3/15/2010 14:43 Opera/9.80 (Windows NT 5.1; U; en) Presto/2.2.15 V...   link_link          TYTIP
   12   xxx.xxx.xx.xx   3/15/2010 14:43 Opera/9.80 (Windows NT 5.1; U; en) Presto/2.2.15 V...   link_link          TITPE
   13   xxx.xxx.xx.xx   3/15/2010 14:43 Opera/9.80 (Windows NT 5.1; U; en) Presto/2.2.15 V...   link_link          XAIP
   14   xxx.xxx.xx.xx   3/15/2010 14:44 Opera/9.80 (Windows NT 5.1; U; en) Presto/2.2.15 V...   link_link          TYTIP
   15   xxx.xxx.xx.xx   3/15/2010 14:44 Opera/9.80 (Windows NT 5.1; U; en) Presto/2.2.15 V...   link_link          TYTIP
   .....1000's of Other Records

Can you make a predefined list of url_id?

Because then you can do:

DELETE FROM table_name
WHERE url_id = 'TITPE'
ORDER BY time DESC
LIMIT 10,18446744073709551615;

This will delete in reverse order, skipping the first 10 rows (and since it's in reverse, those are the most recent rows).

PS. Be sure to test this before trying it on live data.

If you are trying to save a very small subset of records and delete the rest, it's much more efficient (in every database engine I'm familiar with) to select those records you're keeping into a temp table, drop and re-create the original table, and then select the records back in.

So for instance something like this:

DROP TABLE IF EXISTS records_temp;
CREATE TEMPORARY TABLE records_temp LIKE original_table;
INSERT INTO records_temp SELECT * FROM original_table WHERE URL_ID = "TYTIP" ORDER BY ID DESC LIMIT 10;
(then do that again for each type)
DROP TABLE original_table;
CREATE TABLE original_table LIKE records_temp;
INSERT INTO original_table SELECT * FROM records_temp;
DROP TABLE records_temp;