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(a)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).
--
Ilmari Karonen