I'm curious about what guarantees MySQL (and the SQL standard) provide with regard to multiple calls to NOW() within the same statement. If the statement takes a long time to execute, will NOW() be consistent across all invocations? The trivial example of this is if you're updating a large set of rows to an expression involving NOW(), but I'm more interested in INSERT and SELECT semantics. A simplified version of a more subtle example is:
INSERT INTO order_sums (start_date, end_date, total) SELECT [literal date], NOW(), SUM(order_amount) FROM orders WHERE date_of_order >= [literal date] and date_of_order < NOW() The idea is that you've got an 'orders' table that gets a large amount of traffic- presumably thousands of delayed INSERT statements per hour- and users sometimes want to view a summary of the table. Instead of summarizing a huge number of rows every time, you keep caches of the summary info in another table. You'd lock the order_sums table, find the largest end_date, perform the above statement with the end_date substituted for [literal date], unlock the table, and then summarize the order_sums table. There are a couple of places that inconsistent NOW() values could cause erroneous results and an inconsistent database. First, the final 'date_of_order < NOW()' is explicitly coded as a less-than and not a less-than-or-equal-to because there will very likely be additional insertions run before the end of the current second, so we leave the interval open at the top to ignore the current (partial) second. Does this in fact do what I want in the presence of many statements of the form INSERT DELAYED INTO orders (date_of_order, order_amount) values(NOW(),'123') or must I offset the NOW() by something safe like a minute to ignore inserts that haven't quite made it in yet? Second, it is obviously quite important that the NOW() in the select list exactly match the NOW() in the WHERE clause, since the next summary cache entry will be computed using its value. Is this guaranteed? Is it possible (and efficient) to alias the NOW() column in the SELECT list for use in the WHERE clause? How would that work? I'd also love any suggestions/criticisms of the above architecture. I'm trying to address what is a potentially significant performance issue for us (we hope to allow the main table to grow to tens to hundreds of millions of rows before pruning it down), but there might be a much simpler way to approach the problem. Experiences implementing solutions to similar problems would be much appreciated. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
