Right - that was the line I was trying to find earlier. So much for my theory about ignoring the LIMITs :-(
All I can think of to explain the speedup that people (including myself) tend to see anyway is the indexes being cached in the key_buffer the second+ times around. Issac Jeff wrote: > -------- Original Message -------- > From: Perrin Harkins <[EMAIL PROTECTED]> > Subject: Re:Database transaction across multiple web requests > Date: Mon Apr 03 2006 03:55:54 > >> How do you know it isn't just the operating system caching the disk >> fetches? The docs certainly make it sound like a query cached with >> LIMIT will not be useful when different LIMIT values are applied. >> > > Having looked at the docs, at least for the v4 server, I have to agree > with Perrin. The docs don't specifically mention LIMIT, but they do say > that the SQL has to match, byte for byte. They give an example: > SELECT * from mytable > and > Select * from mytable > and say that the second statement will NOT use the first statements > cache because it does not match byte-for-byte (case is different). At a > guess, they probably just MD5 the SQL and add in some basic checks (for > MD5 collision) to identify the result set in the cache. Here is the URL > > http://dev.mysql.com/doc/refman/4.1/en/query-cache-how.html > > Saying that though, we have a highly interactive webapp, with about 100 > active users per day from a user base of just under 1000. In the last 60 > days, the qcache_hits shows 67 million hits. In our case, the underlying > data changes reasonably frequently. I believe that we would notice a > difference if the qcache was turned off! On a site with more static > underlying data, you would expect even better results. > > As to the OS file cache - that works well too! If MySQL can't satisfy > the query from cache, it will query files that the OS has cached in > memory, meaning that even the fallback is better than disk access > speeds. We run about 20G of database per db server, and there is usually > about 2G of file cache memory in use. > > For us, MySQL performance is blistering! Having worked for years with > Sybase, on big expensive hardware, my subjective feeling is that we get > about 10x the update and about 5x the read access on hardware that costs > 1/10th the price, with 1/10th of the db tuning required. My Oracle > experience is less extensive, but I generally found Oracle was not much > faster than Sybase, but did have a bigger db admin overhead, and for > some reason seemed to always end up with bloated apps. > > Regards > Jeff
