mysql simple query took longer time

Question

select marksymbol,round(avg(p.closerate * p.out_share),2)
from price p
where trandate >= '2000/06/05' and trandate <= '2012/06/22' and p.markcode='RD'
group by marksymbol

It took 0.064s on a local machine with 2gb ram, it took 1 min 30.38 sec on the server.

I am running Windows on my local machine. The server runs MySQL under Linux.

The database scheme is the same on both machines. They have the same indexes and primary keys.

What other factors could affect the performance of this query?

Answer 1

They are indexed correctly, but are they the same size? Is the table on the Linux server significantly bigger than the table on the testing Windows server?

There are a few places you can look for which can help to solve the problem.

1 : To the query itself, you EXPLAIN to let mysql tell you what is going on.

http://dev.mysql.com/doc/refman/5.0/en/explain.html

If you have phpmyadmin installed than you can just click "explain" and it will do that automatically for you (with pretty pie chart) and you can identify the bottleneck that way.

2 : Memory usage on the server. Use the command

free -m

and

vmstat

to see how much memory is available. Your system could have been doing the expensive swapping for your query.

3: Optimize the query itself. From the look of it, you are using string comparison for date search. You can utilize the powerful Datetime feature in mysql to speed up your date comparison. However, I doubt this is the bottleneck for you, so check the top two options first.