On Thu, 2002-11-21 at 09:47, Johannes Ullrich wrote: > And more difficult, try to look at your application design and try > to come up with 'cache tables' that are generated by a cron job > periodically and are used for most queries.
This is an excellent suggestion and may make you think about your application in a different way. But beware -- sometimes the code that is used live doesn't translate well to generating cache/precalculation/summary tables. We have a directed graph stored in the database as a list of vertexes. To generate a summary table showing all the paths through the graph, I used code directly from our front end system (most of the front end system only needs to move over one branch of the graph). This didn't scale well to having to traverse the entire graph. With the summary table, iteratively traversing the graph wouldn't be necessary anyway. It was taking over 1,200,000 queries to traverse the graph using SQL iteratively/recursively, which was pushing the load on the machine to over 3 and was taking 45 minutes to run, even though all the tables were indexed, 'explain' reported no full table scans or file sorts, and none of the individual queries appeared in the slow query log. (the queries were simple, no complex joins, of the nature 'select sub from graph where super = <constant>'). The solution was to use perl to read the tables (and thus the graph) into perl's memory (as perl data structures: arrays of references to arrays to references to arrays, etc) and traverse the graph in perl directly. Then perl generates the data for the new summary table. Doing it this way cut down the run time to 18 seconds! The overhead of parsing all the SQL queries and the connection overhead and just generally having to go to the database everytime was too much. The lesson I learned here: when all the data is at your disposal at once, it pays to rethink the problem and your data structures. --------------------------------------------------------------------- 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
