Prioritise your data
MySQL has several extension keywords (which means they may not work on other SQL servers) which allow you to delay data being updated in the database.
DELETE LOW_PRIORITY FROM Table WHERE ID = 235;
SELECT HIGH_PRIORITY FROM Table ORDER BY Name;
INSERT LOW_PRIORITY INTO Table VALUES (Foo);
INSERT DELAYED INTO Table VALUES (Foo);
UPDATE LOW_PRIORITY Table SET Password = 'g0d';
Those six queries all alter the default querying system in various ways. The LOW_PRIORITY keyword will make our DELETE and INSERT statements have slightly less priority than SELECT statements, which means that they are only suitable for writes that you don't mind waiting for. Low priority writes can potentially never happen because SELECT queries may always be slipping in ahead of them. One key thing to note about low priority writes are that the client sending the write (a PHP script in our case) will stop processing at the mysql_query() function until the write takes place.
HIGH_PRIORITY is used for SELECT statements only, and allows you to force reads ahead of writes - this is perfect for very important queries that you need performing immediately, and, as long as you are sparing about how you use high priority reads, they are very helpful.
The last option is the DELAYED keyword is for INSERT statements, and works similarly to LOW_PRIORITY in that the writes have lower priority than reads, however they are otherwise very different - INSERT DELAYED statements are added to a special query queue, and mysql_query() returns control back to the PHP script immediately so you can carry on doing work. Then, when the query queue has hit a certain size, or a certain length of time has passed, all the queries in the queue are executed at normal priority.
Many, many sites (especially high-volume ones such as Slashdot), have notices like "Your comments may take 5 minutes to appear", or "Your account will be deleted within 24 hours", etc. People are used to it, and don't mind - but on your side of things, it lets your MySQL database leave less important queries till it has some free time.
Next chapter: Optimise your tables >>
Previous chapter: Optimising your SQL
Home: Table of Contents