At 03:25 PM 1/11/2009, you wrote:
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?
I wanted to see what the default settings would do.
> 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.
I discovered the problem I'm having is the query optimization in 5.1! MYSQL
5.01 had no problem with these queries.
My MyISAM table checked out ok and I optimized it to rebuild the indexes
under 5.1. So there is nothing wrong with the table.
I started playing with the SQL and noticed if I take out either the "Limit
1" clause or the "Order by" clause, it runs in about a second.
So it works fine if *either* the "Limit 1" or "Order by" clause is used,
but NOT BOTH! If both are used the query time goes from under 1 second to
around 1 hour. This is odd because it looks like it is doing a full table
scan and ignoring "where prod_code='ABC' " which should limit the rows
returned to 14,000 rows. So sorting on 14,000 rows and putting a limit on
it should not make the query run 1000x slower.
I started tweaking the SQL and took out most of the Where Clause to get:
select purch_date from items where prod_code='ABC' order by purch_date
limit 1;
and it takes 10 seconds. There are 14,000 rows where prod_code='ABC'. This
is pretty slow since it is returning only 1 row. An Explain show it is
using the ix_Date Key (columns Purch_Date,Store_Id,Prod_Code) because of
the sort clause. It is using the index for sorting so it has to scan a lot
more rows because now it is scanning all products and not just
prod_code='ABC'. So the Order By clause is superseding the Where clause!
If I remove the Order By clause to get
select purch_date from items where prod_code='ABC' limit 1;
then the Explain uses the ix_Main index (columns Prod_Code, Store_Id,
purch_date) and gets the results in 0 ms.
I am resetting the query cache between tests to make sure the results are
accurate.
I then went back to the original SQL statement and forced MySQL not to use
the date index for sorting. So instead of taking more than an hour to
complete it now completes in 90ms. I may have to do the same with other SQL
statements if this problem crops up there too.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
