On Tue, Jul 13, 2010 at 8:22 AM, mos <mo...@fastmail.fm> wrote: > At 09:29 AM 7/13/2010, æ Žå¾ wrote: >> >> Hello, >> >> There are more than 2m records in the table -- fxrate. >> I create patitions, indexes, but it still takes me about 7 minutes to >> execute the following query >> SELECT COUNT(*) >> FROM fxrate >> WHERE MONTH(quoteDate) = 6 >> AND quoteHourMinSec BETWEEN "06:00:00" AND "19:00:00" >> >> result: 647337 >> >> How can I improve the performace? >> Thanks in advance. > > > > It is because you are using MONTH(quoteDate) which means it has to go > through all the rows. You should add a compound index to > QuoteDate,QuoteTime. > > Then Try > > select count(*) from fxrate where quotedate between '2010-06-01' and > '2010-06-30' and quotetime between '06:00:00' and '19:00:00' > > I'm not sure why you have two columns for storing the date and time. I would > have used one column QuoteDateTime as DateTime. > I also don't know why you have all those partitions for a small 2m row > table. > > Mike > >
A compound index index will not be used after the first range condition. I agree about using a single datatype (datetime or timestamp) and partitioning not being a good idea for only 2M rows. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
