On Thu, May 8, 2008 at 2:33 PM, catrope@svn.wikimedia.org wrote:
Revision: 34431 Author: catrope Date: 2008-05-08 12:33:20 +0000 (Thu, 08 May 2008)
Log Message:
API:
- Added ApiQueryBase::addJoin() which provides a cleaner interface to construct JOIN queries. Behind the scenes this still uses the old, ugly way, but it'll be easy to rewrite when/if the Database class gets its own function for JOINs
- Used addJoin() in query modules where necessary
- Removed FORCE INDEX (rc_timestamp) from ApiQueryRecentchanges: it's nigh impossible to integrate with addJoin() and it doesn't seem to be necessary anyway (my MySQL instance automatically chooses rc_timestamp)
I've heard rumours on #wikimedia-tech that MySQL 4 sometimes chooses the wrong index unless forced. So I don't know whether this actually works.
Bryan
Bryan Tong Minh schreef:
I've heard rumours on #wikimedia-tech that MySQL 4 sometimes chooses the wrong index unless forced. So I don't know whether this actually works.
Well someone's gotta check this on MySQL 4 then. All I know is that it works on MySQL 5.0.45, which is what I use.
Roan Kattouw (Catrope)
On Thu, May 8, 2008 at 2:30 PM, Bryan Tong Minh bryan.tongminh@gmail.com wrote:
I've heard rumours on #wikimedia-tech that MySQL 4 sometimes chooses the wrong index unless forced.
*Every* database engine is going to make the wrong choice sometimes, unless maybe it uses some kind of adaptive technique (I'm not an expert on DBMSes). The question is only how often, and in which particular cases? MySQL 4 will choose the wrong index sometimes when MySQL 5 will not. (Possibly the converse is true as well.)
My approach is that if it chooses the right index for me, it's up to Domas or someone to fix it if he sees it's broken on MySQL 4. :)
My approach is that if it chooses the right index for me, it's up to Domas or someone to fix it if he sees it's broken on MySQL 4. :)
the problem is that API developers add more and more various dynamic query building features, and we hit problems far more in API, than in regular code. Our regular code is just following well known usage patterns, whereas API allows to invent them, and we end up hitting bad index. Today some API logging table queries did overload few database servers. If I really have to go and fix API code, I'll do it with axe. :)
Anyway, FORCE INDEX is needed, when WHERE condition doesn't provide any(/much) selectivity, compared to other indexes, but using index would provide good ORDER BY .. LIMIT optimization.
Domas Mituzas schreef:
My approach is that if it chooses the right index for me, it's up to Domas or someone to fix it if he sees it's broken on MySQL 4. :)
the problem is that API developers add more and more various dynamic query building features, and we hit problems far more in API, than in regular code. Our regular code is just following well known usage patterns, whereas API allows to invent them, and we end up hitting bad index. Today some API logging table queries did overload few database servers. If I really have to go and fix API code, I'll do it with axe. :)
If you notice any bad API queries, please report them to me, preferably accompanied by some indication as to how to fix them (SQL-wise), either through BugZilla (set Component=API), the API list ( mediawiki-api@lists.wikimedia.org ) or personal e-mail ( roan.kattouw@home.nl ).
Roan Kattouw (Catrope)
wikitech-l@lists.wikimedia.org