Best way to update record X when Y is inserted

I have a huge table that is mainly used for backup and administrative purposes. The only records that matters is the last inserted record.

开发者_开发技巧

On every hit to order by time inserted is just too slow. I want keep a separate table with the last inserted id.

In PHP I now insert, get last inserted id, and update the other table.

Is there a more efficient way to do this.

---

You could do this on the database end by using a trigger.

---

(Sorry about posting this as a separate answer, was a bit too long for a comment on Matti's answer.)

There is a small performance overhead associated with triggers, but if I recall correctly it's fairly negligible for normal use (depending on what you're doing with it of course). Mostly it'd only be a problem if you're performing bulk uploads (in which case you'd usually drop/disable the triggers for the duration of the task). Seems to me that the overhead here would be very minimal seeing as you're only really performing one INSERT/UPDATE on X in addition to the INSERT on Y.

Essentially, a trigger will scale a lot better compared to your current method because instead of having to perform a lookup to find the last updated record you can just perform the insert operation, then directly insert the primary key of the new record into the "last updated" table.

---

Why don't you add an index on that field?

Quick seach and sort is exactly what an index is for.

Updating your own 'pseudo-index' in a table amounts to re-inventing the wheel.

Besides, adding a trigger to a DB always feels very dubious (as in un-obvious) to me!