Page MenuHomePhabricator
Paste P997

various sql queries for T105964
ActivePublic
Actions

Authored by Reedy on Jul 16 2015, 2:28 PM.
Tags
None
Referenced Files
F193278: various sql queries for T105964
Jul 16 2015, 2:28 PM
Subscribers
marcmiquel
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 edited the content of this paste. (Show Details)Jul 16 2015, 2:28 PM
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.
marcmiquel subscribed.Jul 16 2015, 2:37 PM

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

Reedy added a comment.Jul 16 2015, 2:53 PM
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]>
Reedy mentioned this in T105964: HELP! Database is getting Slow: A test which took less than 10 min, now it takes 3 hours. I cannot do my experiments..Jul 16 2015, 2:55 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits