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.WP@daniel.baur4.info 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).