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.
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'.
I copied these tables over from the MySQ 5.0 data directory to the MySQL
5.1 data directory.
My question is this. Has the table or index structure changed in 5.1? Do I
have to optimize all of the tables before using 5.1? Did I overlook
something when installing 5.1 that would account for this 1000x speed
slowdown? I thought I'd bounce this question off the group before I start
tearing things apart here to find out what is going on.
I'm running a check now on the tables to see if they are damaged but I'd be
grateful if someone could chime in and let me know if I overlooked
something simple so I could have a "d'uh moment" and put this behind me.
TIA
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
