MySQL performance: caching and long queries

[mnalsky]

Hello,

I have very simple query that runs first time over 2 minutes and second time - 3.65 seconds.

Query joins 3 tables, 2 million, 300,000 and 1,000 rows. The size of the whole database (20 tables) on disk - 200Mb.

If server has no activity for 24 hours and then come with the same query - it again runs it for 2 minutes.

Does anyone know, how to make mysql server cache all tables forever? So that this particular query will run for 4 seconds until server restart.

Thanks,
Max Nalsky

P.S. Please find query attached.

----------------------------------

SELECT
transaction.accountSource, transaction.accountDest,
transaction.currency1, transaction.currency2,
CASE WHEN opDate < '1999-09-08'
THEN 0 ELSE 1 END as datePosition,
SUM(transaction.amount1Fwd), SUM(transaction.amount1Back),
SUM(transaction.amount2Fwd), SUM(transaction.amount2Back),
analiticPredefinedValues1.value
FROM
transaction,
analiticPredefinedValues as analiticPredefinedValues1,
transactionAnalitic as transactionAnalitic1
WHERE
storn = 0 AND opDate <= '2012-11-04'
AND transaction.ID = transactionAnalitic1.transaction
AND transactionAnalitic1.analitic = analiticPredefinedValues1.ID
AND analiticPredefinedValues1.analitic = 14
GROUP BY
analiticPredefinedValues1.value,
transaction.accountSource,
transaction.accountDest,
transaction.currency1,
transaction.currency2,
datePosition

 

[Bastien]

you can set some of the server parameters to tune the sql including the cache size and time

see

http://www.mysql.com/doc/en/Server_parameters.html

for more details.

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[vanekl]

Add to my.ini file:

query_cache_size=XXM

where XX is the number of megabytes of RAM you want to reserve
for cache.