MySQL Forums
Forum List  »  Optimizer & Parser

Re: index choose and analyze table
Posted by: Jens Schanz
Date: September 19, 2006 04:34AM

Hi Toa,

i think the output from "explain" is quite right. If explain says no index will be used, the following select won't use an index. After an analyze table the statement could access an index.

During 07:00 and 22:00 we only have a lot of updates and inserts, just a few selects. The reports are running between 0:00 and 06:00. If I do no analyze table, the statistics are wrong and the queries couldn't access any index (just tried to give the select a hint to the index - but doesn't work).

This seems to me as a very strange, but comprehensible behavior. Each time I insert or update an record, the index will be accessed and updated. So during this huge load of inserts and updates (800 qps) the access path will be optimized for this and not for 3 or 4 selects.

I'll fix this with an analyze table before the reports run.

Thanks ...


Brgds,
Jens

Options: ReplyQuote


Subject
Views
Written By
Posted
3263
September 11, 2006 04:37AM
2548
September 14, 2006 08:25AM
10689
September 14, 2006 10:38AM
Re: index choose and analyze table
3283
September 19, 2006 04:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.