Page MenuHomePhabricator
Paste P997

various sql queries for T105964
ActivePublic

Authored by Reedy on Jul 16 2015, 2:28 PM.
mysql:wikiadmin@db1052 [enwiki]> explain select 'en', user_editcount from user where user_name = 'Reedy';
+------+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | user | const | user_name | user_name | 257 | const | 1 | |
+------+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql:wikiadmin@db1052 [enwiki]> explain select 'en', user_editcount from user where user_name LIKE 'Reedy';
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| 1 | SIMPLE | user | range | user_name | user_name | 257 | NULL | 1 | Using index condition |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)
mysql:wikiadmin@db1052 [enwiki]> explain select 'en', user_editcount from user where user_name LIKE 'Reedy%';
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| 1 | SIMPLE | user | range | user_name | user_name | 257 | NULL | 80 | Using index condition |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql:wikiadmin@db1052 [enwiki]>

Event Timeline

Reedy changed the title of this paste from untitled to various sql queries for T105964.
Reedy updated the paste's language from autodetect to sql.

SELECT "eswiki",user_editcount FROM eswiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "enwiki",user_editcount FROM enwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "frwiki",user_editcount FROM frwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "itwiki",user_editcount FROM itwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "plwiki",user_editcount FROM plwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "nlwiki",user_editcount FROM nlwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "dewiki",user_editcount FROM dewiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "ruwiki",user_editcount FROM ruwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "ptwiki",user_editcount FROM ptwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "euwiki",user_editcount FROM euwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "zhwiki",user_editcount FROM zhwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "glwiki",user_editcount FROM glwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "ocwiki",user_editcount FROM ocwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "anwiki",user_editcount FROM anwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "huwiki",user_editcount FROM huwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "ukwiki",user_editcount FROM ukwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "warwiki",user_editcount FROM warwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "arwiki",user_editcount FROM arwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "viwiki",user_editcount FROM viwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "svwiki",user_editcount FROM svwiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "rowiki",user_editcount FROM rowiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "mswiki",user_editcount FROM mswiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "fawiki",user_editcount FROM fawiki_p.user WHERE user_name LIKE %s UNION ALL SELECT "cawiki",user_editcount FROM cawiki_p.user WHERE user_name LIKE %s ORDER BY user_editcount DESC

MariaDB [eswiki_p]> SELECT "eswiki",user_editcount FROM eswiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "enwiki",user_editcount FROM enwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "frwiki",user_editcount FROM frwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "itwiki",user_editcount FROM itwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "plwiki",user_editcount FROM plwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "nlwiki",user_editcount FROM nlwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "dewiki",user_editcount FROM dewiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "ruwiki",user_editcount FROM ruwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "ptwiki",user_editcount FROM ptwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "euwiki",user_editcount FROM euwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "zhwiki",user_editcount FROM zhwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "glwiki",user_editcount FROM glwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "ocwiki",user_editcount FROM ocwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "anwiki",user_editcount FROM anwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "huwiki",user_editcount FROM huwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "ukwiki",user_editcount FROM ukwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "warwiki",user_editcount FROM warwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "arwiki",user_editcount FROM arwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "viwiki",user_editcount FROM viwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "svwiki",user_editcount FROM svwiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "rowiki",user_editcount FROM rowiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "mswiki",user_editcount FROM mswiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "fawiki",user_editcount FROM fawiki_p.user WHERE user_name LIKE 'Reedy' UNION ALL SELECT "cawiki",user_editcount FROM cawiki_p.user WHERE user_name LIKE 'Reedy' ORDER BY user_editcount DESC;
+---------+----------------+
| eswiki  | user_editcount |
+---------+----------------+
| enwiki  |          33587 |
| dewiki  |             13 |
| frwiki  |             10 |
| ruwiki  |              7 |
| plwiki  |              6 |
| arwiki  |              5 |
| huwiki  |              4 |
| eswiki  |              3 |
| ptwiki  |              1 |
| zhwiki  |              1 |
| svwiki  |              1 |
| ocwiki  |              1 |
| nlwiki  |              1 |
| warwiki |              0 |
| euwiki  |              0 |
| viwiki  |              0 |
| itwiki  |              0 |
| glwiki  |              0 |
| rowiki  |              0 |
| anwiki  |              0 |
| mswiki  |              0 |
| fawiki  |              0 |
| ukwiki  |              0 |
| cawiki  |              0 |
+---------+----------------+
24 rows in set (0.17 sec)

MariaDB [eswiki_p]> SELECT "eswiki",user_editcount FROM eswiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "enwiki",user_editcount FROM enwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "frwiki",user_editcount FROM frwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "itwiki",user_editcount FROM itwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "plwiki",user_editcount FROM plwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "nlwiki",user_editcount FROM nlwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "dewiki",user_editcount FROM dewiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "ruwiki",user_editcount FROM ruwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "ptwiki",user_editcount FROM ptwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "euwiki",user_editcount FROM euwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "zhwiki",user_editcount FROM zhwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "glwiki",user_editcount FROM glwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "ocwiki",user_editcount FROM ocwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "anwiki",user_editcount FROM anwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "huwiki",user_editcount FROM huwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "ukwiki",user_editcount FROM ukwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "warwiki",user_editcount FROM warwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "arwiki",user_editcount FROM arwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "viwiki",user_editcount FROM viwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "svwiki",user_editcount FROM svwiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "rowiki",user_editcount FROM rowiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "mswiki",user_editcount FROM mswiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "fawiki",user_editcount FROM fawiki_p.user WHERE user_name = 'Reedy' UNION ALL SELECT "cawiki",user_editcount FROM cawiki_p.user WHERE user_name = 'Reedy' ORDER BY user_editcount DESC;
+---------+----------------+
| eswiki  | user_editcount |
+---------+----------------+
| enwiki  |          33587 |
| dewiki  |             13 |
| frwiki  |             10 |
| ruwiki  |              7 |
| plwiki  |              6 |
| arwiki  |              5 |
| huwiki  |              4 |
| eswiki  |              3 |
| ptwiki  |              1 |
| zhwiki  |              1 |
| svwiki  |              1 |
| ocwiki  |              1 |
| nlwiki  |              1 |
| warwiki |              0 |
| euwiki  |              0 |
| viwiki  |              0 |
| itwiki  |              0 |
| glwiki  |              0 |
| rowiki  |              0 |
| anwiki  |              0 |
| mswiki  |              0 |
| fawiki  |              0 |
| ukwiki  |              0 |
| cawiki  |              0 |
+---------+----------------+
24 rows in set (0.18 sec)

MariaDB [eswiki_p]>