At 2:45 PM +1000 7/3/07, Daniel Kasak wrote:
>On Mon, 2007-07-02 at 21:19 -0700, Ed Lazor wrote:
>
>> I have a 400mb database. The first query to tables takes about 90 seconds.
>> Additional queries take about 5 seconds. I wait a while and run a query
>> again; it takes about 90 seconds for the first one and the rest go quickly.
>> I'm guessing data is being loaded into memory which is why things speed up.
>>
>> Does this sound right? Is there a way to keep the table in memory? Nothing
>> is changing in the data.
>
>You probably have the query cache enabled already if it's behaving like
>this. See: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
The same behavior might be observed without the query cache being enabled.
The key buffer caches MyISAM index blocks, filesystem caching is used
for data blocks, etc.
>
>The query cache only works for the current connection, so if you open a
>connection and execute a query, it's only cached for *that* connection.
It's not cached only for that connection, actually.
>
>But you should get MUCH better performance than what you're currently
>getting anyway. You need to look at your queries, and put indexes on
>appropriate fields. Generally you want them on fields used in joins, and
>fields in your 'where' clause.
Always a good idea.
Also, you might want to increase your server buffer sizes as appropriate.
See, for example:
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
