Re: performing mysqldump corrected query issue?
> 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.
Subject
Views
Written By
Posted
2607
June 26, 2012 01:12PM
1268
June 26, 2012 02:08PM
Re: performing mysqldump corrected query issue?
1465
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.