On Sun, Jan 11, 2009 at 2:24 PM, mos <mo...@fastmail.fm> wrote: > Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to 5.1.30 > because after all, it was Saturday and what else is there to do on a > Saturday? > > I'm running it on XP Pro with 3gb ram and used the my.ini for very large > system. I decided not to tweak any of these settings except for the datadir > and I commented out skip-networking since my old my.ini file didn't have it > either. I'm using only MyISAM tables so skip-innodb is enabled.
Why didn't you re-use your my.ini from your 5.0 system? > Well to make a sad story short, I ran my application last night and returned > this morning only to find it still running. Select queries that would run on > 1 table to return 1 row should take under a second, now takes over an hour. > An Explain shows that it is using the index. > > The query goes something like this: > select purch_date from items where prod_code='ABC' and ((store_id='A' and > purch_date>'2007-01-01') or (store_id='B' and purch_date>'2007-01-05') or > (store_id='C' and purch_date>'2007-01-09')) and (col1 is null or col2 is > null or col3 is null or col4 is null or col5 is null or col6 is null) order > by purch_date limit 1; > > There are 2 compound keys: prod_code,purch_date,store_id and > prod_code,purch_date,store_id > The table items has approx 30 million rows in it and there are approx 5,000 > rows for 'ABC'. We have a client in a similar situation but I'm still waiting for access to the server to investigate the problem. I suspect that EXPLAIN is lying, in the client's case, but I can't know until I get on the box and see. In your case I can't comment. It sounds like the same thing but it might not be. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
