El Mié 02 Mar 2005 16:04, escribió: > Hmmmm, sounds like you are trying to mix OLTP and OLAP in one database > structure. That's a tough one. You want your tables designed to always > accept data in real time, but once the data is in, it doesn't change > and you want to query it. Relational vs. Dimensional data models. > > Your hardware is pretty good. Sorry, I missed the early thread > responses, did you figure out where things are bottlenecking (CPU, Disk > I/O, RAM, Network)? That will help you focus on what you can change in > your software if you can't upgrade your hardware. > > The first place to always look is your queries. Optimizing your queries > always gives you the best bang for you buck. Use explain to make sure > MySQL is using the right indexes, especially since you are using date > ranges. Sometimes MySQL may use the best index, sometimes it won't, > simply by changing the date range. It won't hurt to use hints (USE, > FORCE, IGNORE) in your query if you know you want MySQL to use a > certain index. > You could possible also change you structure slightly, like add a > WeekNumber column. It could just be an incrementing week number with > 1/1/2004 being week 1, 1/1/2005 being week 53, etc. So it would be > weeks since 1/1/2004. It could be just a regular int type, which should > be quicker than searching on a date field. The idea is to add constants > on entry to speed up the summaries. Also, try to eliminate any and all > calculations from your query, like replace datesub(now(), interval 12 > month) with a constant. Which means figuring out the right date before > hand. > > Would you be able to run daily summaries? Then your weekly summaries > are just running against 7 records. > > 1 million rows is not that big, so you should be able to get good > performance, it's just a matter of structuring things correctly. > > Heck, it may end up that the best thing to do is an insert select into > another table (maybe even a temp table), which you then run your > summaries against. Since your "dump" is sequential access to disk (the > same order the data was entered), it may be very quick.
After some thought, and seeded by the many fine suggestions from the list, I decided to restructure completely the approach to the problem. The result is that the query to calculate one variable now takes only 7 minutes! We will go on and restructure the remaining 10 variables and see how it goes. Sometimes, not working makes you more productive ;-) Thank you all and best regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
