MySQL Forums
Forum List  »  Optimizer & Parser

Re: performing mysqldump corrected query issue?
Posted by: Rick James
Date: July 01, 2012 06:43PM

> All joins are done over indexes, so joining shouldn't be an issue here.
Naive assumption.

> I performed a mysqldump...
That forced it to remove any 'free' space from the table and rebuild the indexes. OPTIMIZE TABLE would have done the same thing (in a different way).

MyISAM or InnoDB?

On MyISAM tables, statistics can atrophy over time. ANALYZE TABLE can solve that. (OPTIMIZE does that, but does more stuff.) ANALYZE reads the entire table.

On InnoDB tables, the "8 random dives" sometimes comes up with poor statistics. ANALYZE TABLE can _temporarily_ fix that. There is no permanent fix. ANALYZE does 8 dives, so it is quite fast.

STRAIGHT_JOIN, USE INDEX, and FORCE INDEX are brute force solutions that should be avoidable and should be avoided 99% of the time. The downside of them is that you are restricting the optimizer's choices. If the data changes over time; the choice you are pushing may no longer be the optimal one.

Options: ReplyQuote

Written By
Re: performing mysqldump corrected query issue?
July 01, 2012 06:43PM

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.