heap - Choose Mysql engine for processing a big "type-value" table -

my task delete entities not affected during operation database. created separate table have 2 columns, first represets name of table , second id of record in table.

create table edited_entities (         table varchar(50) not null,          id bigint(20) not null) 

for example if have table

create table puppy(         id bigint(20) not null,          name varchar(20) not null) 

and record in it

id | name 1  | rex 

if edit record put following data edited_entities:

table | id puppy | 1 

then need delete non affected entities (which ids not in edited_entities table) , following:

delete puppy id not in      (select ee.id edited_entities ee ee.table= 'puppy'); 

i wonder best engine such kind of operation (mysql)? default db engine innodb. thought memory (heap) not sure if can faster delete operation.

if have suggestion how can optimise required operation glad here it.

i don't whant add additional columns puppy table.

memory will faster, since doesn't have go disk @ end of transaction. in case i'd try first btree , not hash index, because lets use partial composite indexes, on-disk tables.

also try prepared statements insert , delete operations: prepare 1 of each before processing , invoke relevant parameters. might faster since doesn't have parse sql; there cases whole system gets little slower because take non-trivial amounts of memory.

another option experimental 'handlersocket' feature, available in several forks of mysql percona server, lets access mysql table nosql store, huge performance benefits, full acid compliance. (the percona people performance fanatics; if can't use handlersocket, sure test fork)

finally, viable (but more work you) option separate in-memory database. i'd tend go redis, high-speed in-memory key-value store added twist 'values' useful data structures. in case, store set of ids each table, like

tokeep:puppy => 1,4,6,76..... 

it's simple (and atomic) operation add element set (sadd tokeep:puppy 76), , @ end fetch them create sql delete id not in (...) operation

and, last option think of (and still more work you), put in same store: there's fork of redis, called redisql, it's alchemy database; adds sql tables redis, keeping of performance benefits of nosql. so, have 'regular' tables sql tables in alchemy, store 'tokeep' sets on nosql on same server, , @ end a:

delete puppy id not in ($smembers tokeep:puppy ); 

bam!