It sounds like you should use triggers.
You'll need three triggers on the large table:
- AFTER INSERT, for each row you'll need to copy the new row into the smaller table and delete the oldest row from the smaller table
- AFTER UPDATE, for each row you'll need to check whether that row is in the smaller table and if it is, make the same updates to it
- AFTER DELETE, for each row you'll need to check whether that row is in the smaller table and if it is, remove it.
For example:
DELIMITER //
CREATE TRIGGER after_insert AFTER INSERT ON big_table
FOR EACH ROW
BEGIN
DECLARE small_table_rows INTEGER;
INSERT INTO small_table (field1, field2) VALUES (NEW.field1, NEW.field2);
SELECT COUNT(*) INTO small_table_rows FROM small_table;
IF small_table_rows > 10000 THEN
DELETE FROM small_table ORDER BY id ASC LIMIT 1;
END IF
END;//
DELIMITER ;
DELIMITER //
CREATE TRIGGER after_update AFTER UPDATE ON big_table
FOR EACH ROW
BEGIN
UPDATE small_table
SET field1 = NEW.field1, field2 = NEW.field2
WHERE small_table.id = NEW.id;
END;//
DELIMITER ;
DELIMITER //
CREATE TRIGGER after_delete AFTER DELETE ON big_table
FOR EACH ROW
BEGIN
DELETE FROM small_table WHERE small_table.id = OLD.id;
END;//
DELIMITER ;
It's fairly straightforward, though the triggers can get unwieldy if the tables have a lot of columns. You may want to consider the performance characteristics of MyISAM vs InnoDB; InnoDB may offer better overall performance depending on what sorts of queries you are running.
You don't want to use views; views in MySQL are not materialized, so you won't generally get any performance benefit (which is what it sounds like you want) and you won't be able to index the view differently from the table(s) on which the view is based.