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.
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!
(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.