* Russ > I'm doing some web development and have mysql (3.23.x) installed > to test with (win2k, but I don't think this question is > particularly platform specific). > > I have a reasonably complex query which I am trying to optimise. > When I first startup mysql, and perform the query, it can take > 10 or so seconds. However, further subsequent queries (sorting > by the same column) return in a fraction of a second. This is true > even with new WHERE or LIMIT clauses. > > Is mysql creating a 'temporary' index and caching it? Is the > original 10 second query the actual time for my query to > execute? I'm finding it difficuly to optimise my query as > I'm not sure exactly which times are correct.
mysql has a shared key buffer, indexes are cached when they are used. The size of the key buffer is controlled by the 'key_buffer_size' variable, I think the default value is 8M. <URL: http://www.mysql.com/doc/en/SHOW_VARIABLES.html > <URL: http://www.mysql.com/doc/en/Server_parameters.html > This is normally a usefull thing, but it makes it hard to evaluate the speed of a query, like you have experienced. One could say there are two execution times which are interresting when testing one single query: The time used when there are no cached keys, and all information must be read from the disks, and the time used when the key buffer is populated with all the keys you need. In addition to this, some queries can gather all information from the index, and need not read the row from disk at all if the index is in the key cache. In the real world, the key buffer is usually partly usefull for any particular query, i.e. somewhere between the two test scenarios, which represents 'worst case' and 'best case', not considering the additional load of multiple users. There are (at least) two major ways to a faster database: configuring the server to optimally utilize your hardware, like adjusting the 'key_buffer_size' variable, and 'configuring' your database by normalizing and indexing. Depending on your application, you may not have a problem. If your key_buffer_size is bigger than the total size of all of your indexes, then you will only have the 10 second delay for the very first query, until the key buffers are loaded. Because subsequent executions are fast in your case, it sounds like you are using indexes. In general, it is better to use EXPLAIN SELECT than to rely on the execution time you experience. There is the key buffer, and there could be other processes on the machine messing up the time, too. EXPLAIN SELECT tells you what is actually going on. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
