On 09/28/2010 03:32 PM, DaB. wrote: > Hello, > At Tuesday 28 September 2010 14:00:46 DaB. wrote: >> On Mon, Sep 27, 2010 at 4:17 PM, DaB.<[email protected]> wrote: >>> I tried it today. It morphs a 1.36s query (simple count-query on my >>> username on dewiki_p) into something I canceled after 2 minutes (I tried >>> both: if- selecting and where-clause). >> >> What exactly did you try? You'd need to change indexes on the >> underlying table for this to work acceptably. > > I run a non-cached simple count-query on my user-name on dewiki (like I told > in my email yesterday) and got a result in a sec. > Then I took our spare db-server… - oh wait, we have no spare db-server. > So I took 1 of the sql-s5-db-servers out of rotation… – oh wait we have only 1 > server for sql-s5. > So I just changed the view of revision (like I told in my email yesterday) in > a way it hides rev_user_text if rev_deleted>0, to test the runtime of a non- > cached simple count. I canceled that after 1 minute or 2. Then I changed the > view in the way that all rows if rev_deleted>1 are hidden (with a where- > clause) and run the non-cached count again and canceled it again after a > minute or two. Then I changed the view back to its original config and run my > non-cached query again and it returned the count in a sec.
There's a much easier way to test that: $ sql enwiki_p ... mysql> select count(*) from revision where rev_user = 398996; +----------+ | count(*) | +----------+ | 13445 | +----------+ 1 row in set (1.19 sec) mysql> select count(*) from revision where rev_user = 398996 and rev_deleted = 0; +----------+ | count(*) | +----------+ | 13445 | +----------+ 1 row in set (2 min 4.93 sec) It doesn't really matter if the "rev_deleted = 0" condition is part of the query itself or included via the view definition. > If you think that is done wrong or not trust me or my results, just try it > yourself, use another db-server or anything. If you find a fast solution, I > will be the last to not implement it. Adding the indexes Aryeh suggested ought to fix it (at the cost that having a bunch of extra indexes generally entails). -- Ilmari Karonen _______________________________________________ Toolserver-l mailing list ([email protected]) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
