On Mon, Sep 15, 2008 at 1:48 PM, Victor Vasiliev vasilvv@gmail.com wrote:
Is it possible to locally create some indexes specific for toolserver? As for now, it will help to seriously speed up most tools. E.g. index (rev_user,rev_page) will speed up all editcounters, which usually uses query like SELECT COUNT(*), page_namespace FROM page JOIN revision ON page_id = rev_page WHERE rev_user = 37880 GROUP BY page_namespace ORDER BY page_namespace;
Why would that index speed up the access noticeably? There's already an index on (rev_user, rev_timestamp) (assuming that the toolserver has stock indexes), and rev_page is in the leaf nodes.
If optimization is needed here, a root should install the microslow patch and do an analysis of what's taking up the most time, and see if those can be indexed or otherwise improved. At any rate, an ALTER TABLE would require considerable downtime, since as far as I know there's only one toolserver database server, so you can't just throw all the load onto the other slaves while you take one out of rotation. (Or are there multiples these days?)