5 subtle ways you’re using MySQL as a queue, and why it’ll bite you | Engine Yard Blog

5 subtle ways you’re using MySQL as a queue, and why it’ll bite you | Engine Yard Blog

Finally, it’s also really easy to avoid the one-big-table syndrome. Just create a separate table for new emails, and when you’re done processing them, INSERT them into long-term storage and then DELETE them from the queue table. The table of new emails will typically stay very small and operations on it will be fast. And if you do the INSERT before the DELETE, and use INSERT IGNORE or REPLACE, you don’t even need to worry about using a transaction across the two tables, in case your app crashes between. That further reduces locking and other overhead. If you fail to execute the DELETE, you can just have a regular cleanup task retry and purge the orphaned row. (Hmm, sounds like another job queue, no?) You can do much the same thing for any type of queue. For example, articles or comments that are pending approval can go into a separate table. This is really required on a la...

Full article: http://www.engineyard.com/blog/2011/5-subtle-ways-youre-u...